March 11, 2012 at 3:08 pm
Hi i m newbie for this forum.
I have query and it has parameter dates
If i change date from 2/1/2003 to 2/29/2012 result will produce in less than 1 sec.
If I change to recent month like 02/01/2012 to 02/29/2012. it takes more than 2 min.
Any help will be appreciate.
Thanks
From
neol
March 11, 2012 at 3:22 pm
Hi neol
if you are able, then please...
provide "create table" scripts
example data
what indexes you have
the query you are actually running
execution plans...actual preferable
the results you are expecting
if the above is not self explanatory...pls post back and we can advise.
and Welcome to the Site...if you help us , we will help you......:-)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 4:13 pm
My guess, you're running into this problem: http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2012 at 4:25 pm
I understand Mr/Ms 10 century. I thought It will some patch will resolve my issue.
Here is detail information:
Select * from tblA where ((rtrim(ltrim(Stats)) <> 'Closed' and convert(datetime, case when isdate(Pdate)=1 then Pdate else null end) < convert(datetime,'3/1/2012')) or (rtrim(ltrim(Stats))='Closed' and (convert(datetime, case when isdate(Dclose)=1 then Dclose else null end) between convert(datetime,'2/1/2012') and convert(datetime,'2/29/2012') or TblAID in (Select Distinct tblAID from tblB where isdate(Pdate)=1 and ltrim(rtrim(Pdate)) > '' and OrdID > 0 and convert(datetime, case when isdate(Pdate)=1 then Pdate else null end) between convert(datetime,'2/1/2012') and convert(datetime,'2/29/2012') or convert(datetime,'2/29/2012') between (Select min(convert(datetime, case when isdate(Pdate)=1 then Pdate else null end)) from tblB where isdate(Pdate)=1 and OrdID >= 0 and tblAID=tblA.tblAID) and (Select max(convert(datetime, case when isdate(Pdate)=1 then Pdate else null end)) from tblB where OrdID >= 0 and isdate(Pdate)=1 and tblAID=tblA.tblAID) or convert(datetime,'2/1/2012') between (Select min(convert(datetime, case when isdate(Pdate)=1 then Pdate else null end)) from tblB where OrdID >= 0 and isdate(Pdate)=1 and tblAID=tblA.tblAID) and (Select max(convert(datetime, case when isdate(Pdate)=1 then Pdate else null end)) from tblB where OrdID >= 0 and isdate(Pdate)=1 and tblAID=tblA.tblAID) or tblAID in (Select Distinct tblAID from tblB where OrdID > 0 and isdate(Pdate)=1 and convert(datetime, case when isdate(Pdate)=1 then Pdate else null end) between convert(datetime,'2/1/2012') and convert(datetime,'2/29/2012'))))))
table tblA Structure:
TblAIDnumeric(18, 0)Unchecked (Primary key)
Pdatechar(10)Checked
Stats char(20) Checked
Dclose char(20) checked
table tblB structure
tblBIDnumeric(18, 0)Unchecked (Primary Key)
tblAIDnumeric(18, 0)Checked
pdatechar(10)Checked
OrdIDrealChecked
Both Primary key is indexed.
This is reference purpose only.
Let me know if you have any answer on this.
March 11, 2012 at 5:41 pm
Wow, with all the converts and functions and case and the complete lack of useful indexes its a wonder this ever performs well...
Can you post the two execution plans please?
p.s. For anyone interested, here's the query in a readable form
SELECT *
FROM tblA
WHERE ( ( RTRIM(LTRIM(Stats)) <> 'Closed'
AND CONVERT(DATETIME, CASE WHEN ISDATE(Pdate) = 1 THEN Pdate
ELSE NULL
END) < CONVERT(DATETIME, '3/1/2012')
)
OR ( RTRIM(LTRIM(Stats)) = 'Closed'
AND ( CONVERT(DATETIME, CASE WHEN ISDATE(Dclose) = 1 THEN Dclose
ELSE NULL
END) BETWEEN CONVERT(DATETIME, '2/1/2012')
AND CONVERT(DATETIME, '2/29/2012')
OR TblAID IN ( SELECT DISTINCT
tblAID
FROM tblB
WHERE ISDATE(Pdate) = 1
AND LTRIM(RTRIM(Pdate)) > ''
AND OrdID > 0
AND CONVERT(DATETIME, CASE WHEN ISDATE(Pdate) = 1 THEN Pdate
ELSE NULL
END) BETWEEN CONVERT(DATETIME, '2/1/2012')
AND CONVERT(DATETIME, '2/29/2012')
OR CONVERT(DATETIME, '2/29/2012') BETWEEN ( SELECT MIN(CONVERT(DATETIME, CASE WHEN ISDATE(Pdate) = 1 THEN Pdate
ELSE NULL
END))
FROM tblB
WHERE ISDATE(Pdate) = 1
AND OrdID >= 0
AND tblAID = tblA.tblAID
)
AND ( SELECT MAX(CONVERT(DATETIME, CASE WHEN ISDATE(Pdate) = 1 THEN Pdate
ELSE NULL
END))
FROM tblB
WHERE OrdID >= 0
AND ISDATE(Pdate) = 1
AND tblAID = tblA.tblAID
)
OR CONVERT(DATETIME, '2/1/2012') BETWEEN ( SELECT MIN(CONVERT(DATETIME, CASE WHEN ISDATE(Pdate) = 1 THEN Pdate
ELSE NULL
END))
FROM tblB
WHERE OrdID >= 0
AND ISDATE(Pdate) = 1
AND tblAID = tblA.tblAID
)
AND ( SELECT MAX(CONVERT(DATETIME, CASE WHEN ISDATE(Pdate) = 1 THEN Pdate
ELSE NULL
END))
FROM tblB
WHERE OrdID >= 0
AND ISDATE(Pdate) = 1
AND tblAID = tblA.tblAID
)
OR tblAID IN ( SELECT DISTINCT
tblAID
FROM tblB
WHERE OrdID > 0
AND ISDATE(Pdate) = 1
AND CONVERT(DATETIME, CASE WHEN ISDATE(Pdate) = 1 THEN Pdate
ELSE NULL
END) BETWEEN CONVERT(DATETIME, '2/1/2012')
AND CONVERT(DATETIME, '2/29/2012') ) )
)
)
)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 7:33 am
GilaMonster (3/11/2012)
...
p.s. For anyone interested, here's the query in a readable form
...
I would call it "in a more edible, but still noodles form" 🙂
March 13, 2012 at 8:00 am
There is ABSOLUTELY NO WAY the optimizer can routinely produce an optimal plan with that query. SOOOO many things in that query are getting in the way of good optimization. I think optimizing this one will go beyond a forum thread, but others are welcome to give it a try.
Given that query sample, I bet you have lots of other code with similar constructs in your database application. If so I STRONGLY urge you to get a performance tuning professional on board to give your database app a performance review. I imagine there will be lots of low-hanging fruit that can DRAMATICALLY improve the performance and concurrency of your application!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 13, 2012 at 8:18 am
I may be wrong but as written I don't think good indexes would help this query. Just looking at seems to indicate that tables scans are going to be needed to satisfy the conditions in the WHERE clause.
Looking at the tables structure we have a date column defined as a CHAR(10) instead of DATE or DATETIME.
March 14, 2012 at 8:48 am
Hi Guys,
We (me and my DBA) came up with nice solution and it give me result in 0.5 second.
Appreciate your concern.
Thanks
From
Panther
March 14, 2012 at 9:00 am
pantherUSA (3/14/2012)
Hi Guys,We (me and my DBA) came up with nice solution and it give me result in 0.5 second.
Appreciate your concern.
Thanks
From
Panther
Is it something you can share?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply