February 10, 2009 at 9:21 am
Hey
I have this query which takes more than 1 hour to return approx. 300K rows but before a month it used to run fine under 1 min.
Also if i remove the last "Isnull condintion" it runs under 30 secs but if i include that one condition it goes on for ages.
Indexes,statistics are all intact.The box is brand new only 10 days old with enough RAM, process capacity and disk space.
nAlso there is no blocking or locking issues going on and even profiler tracedoes not show anything.
Can anybody please advise me on how to work on this issue?
Select
'colA'=IsNull(col_A,'')
,'colB'=IsNull(col_b,'')
,'colC'=col_C
--remove leading character & zero fill
,'colD'= cast(case when IsNumeric(substring(col_D,1,1))= 0
then dbname.dbo.GetStringFilled(substring(col_D,2,5),6,'0',1)
else col_D
end as char(6) )
,'colE'=Convert(char(8),col_E,112)
,'colF'=col_F
,'coG' = col_G
,'colH' = col_H
into #AP
from dbo.Table A
inner join Table B
on A.CCode = B.CCode
and A.BCode = D.BCode
AND A.Div = D.Div
AND A.VNumber = D.VNumber
and A.CNumber = D.CNumber
and A.CSeq = D.CSeq
left outer join Table C on
B.CCode = C.CCode
AND B.Div= C.Div
and B.VNumber = C.VNumber
AND B.INumber = C.INumber
Where
A.TDate >='2005-01-01'
and IsNumeric(B.INumber) = 1
and IsNull(C.claim,'') <> ''
and len(A.INumber) = 7
and A.Div in (3,11)
and IsNull(C.IType,'') in ('','CLAIM','CLAIMS','CANCELLATIONS','CANCELLATION','CANCELLATIONS','CANCELLATION')
February 10, 2009 at 9:23 am
If is null is the problem, try to change the condition and have a work around 🙂
February 10, 2009 at 9:30 am
There could be a problem with using non-sargable where conditions on your query.
Rather than writing the where conditions with the funciton on the left, try re-writing them using is null on the right of the '='
eg
instead of
IsNull(C.claim,'') <> ''
use
c.claim is not null
IsNull(C.IType,'') in ('','CLAIM','CLAIMS','CANCELLATIONS','CANCELLATION','CANCELLATIONS','CANCELLATION')
use C.ITtype in ('CLAIM','CLAIMS','CANCELLATIONS','CANCELLATION','CANCELLATIONS','CANCELLATION') or C.ItType IS NULL
February 10, 2009 at 9:30 am
Can you post the actual execution plan?
I'd be surprised if you're getting good index usage since you're running several functions against the columns in the WHERE clause, including ISNULL and you've got that rather long IN statement, which is probably translating out as a series of OR's. Also, I assume you're masking the query for some reason because according to the code you're JOINING on a table D that isn't referenced within the FROM clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 9:35 am
Can you post the table and index definitions and the execution plan (saved as a .sqlplan file, zipped and attached)?
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
February 10, 2009 at 9:59 am
I have attahced the execution plan in doc format as for some reason save execution plan is disabled in my server and ald there is no table D ...it was mistyping
Also table A has composite clustered index on Div,Tdate and VNumber
table B has composite clustered index on Div,CNumber and VNumber
table C has composite clustered index on Div,INumber,IType and VNumber
Select
'colA'=IsNull(col_A,'')
,'colB'=IsNull(col_b,'')
,'colC'=col_C
--remove leading character & zero fill
,'colD'= cast(case when IsNumeric(substring(col_D,1,1))= 0
then dbname.dbo.GetStringFilled(substring(col_D,2,5),6,'0',1)
else col_D
end as char(6) )
,'colE'=Convert(char(8),col_E,112)
,'colF'=col_F
,'coG' = col_G
,'colH' = col_H
into #AP
from dbo.Table A
inner join Table B
on A.CCode = B.CCode
and A.BCode = B.BCode
AND A.Div = B.Div
AND A.VNumber = B.VNumber
and A.CNumber = B.CNumber
and A.CSeq = B.CSeq
left outer join Table C on
B.CCode = C.CCode
AND B.Div= C.Div
and B.VNumber = C.VNumber
AND B.INumber = C.INumber
Where
A.TDate >='2005-01-01'
and IsNumeric(B.INumber) = 1
and IsNull(C.claim,'') <> ''
and len(A.INumber) = 7
and A.Div in (3,11)
and IsNull(C.IType,'') in ('','CLAIM','CLAIMS','CANCELLATIONS','CANCELLATION','CANCELLATIONS','CANCELLATION')
February 10, 2009 at 10:41 am
If it's disabled, you must be running the query on SQL Server 2000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 10:44 am
Unfortunately, that image is just shy of useless.
You're getting index scans. That means that the index is not being used effectively. Index scans & hash joins pretty much indicates that the indexes you have and the query you're running, are not working for each other. At least one cause is the functions you're running against the columns, such as checking to see if a column is numeric in the where clause. This will lead to scans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 11:04 am
I agree that the code might not be that efficient but this query was working for more thn 2 yrs and just before a month it went bad.it spiked to 70 mins from 1 min.I can make the changes in code as everybody hassuggested but I need to give out a proper reason for why it has gone bad.Any idea on more troubleshooting.I have tried all the DBA ways including index check, stats check, memroy and spce check, profiler shows nothing, no fragmentation has occured..but still why ??
Thanks to everybody for their replies and efforts!!!1
February 10, 2009 at 11:12 am
MVIR (2/10/2009)
I agree that the code might not be that efficient but this query was working for more thn 2 yrs and just before a month it went bad.it spiked to 70 mins from 1 min.I can make the changes in code as everybody hassuggested but I need to give out a proper reason for why it has gone bad.Any idea on more troubleshooting.I have tried all the DBA ways including index check, stats check, memroy and spce check, profiler shows nothing, no fragmentation has occured..but still why ??Thanks to everybody for their replies and efforts!!!1
Something changed. You applied a service pack or hot fix. A statistics maintenance job was changed from full scan to sampling... something changed. Did the amount of data change? It could be that an index scan worked well enough with only a few rows, but is killing you now?
I'd update the statistics with a full scan on the tables involved and check the fragmentation of the indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 1:10 pm
MVIR (2/10/2009)
I can make the changes in code as everybody hassuggested but I need to give out a proper reason for why it has gone bad.
Very hard to say. Data size has probably reached a tipping point. It does happen.
What version of SQL are you running? (If 2000, why is this in the 2005 forum?)
Are statistics accurate (check the exec plan, see if the actual rows are close to the estimated rows)
What's the wait type that the connection shows while the query's running? (sysprocesses if this is 2000, sys.dm_exec_requests if it's 2005)
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply