Suggestions to reduce query loading time

tmt1982 2014-08-04 17:35:55

I have this sql stored procedure that's taking close to 25seconds to run.
I was hoping to get some suggestions that might help to reduce the run time as I've tried breaking out some of the stuff to a seperate temp table but it doesn't seem to make much of a difference

Aaron Bertrand 2014-08-04 17:45:06
Any chance you can upload a non-anonymized query plan? This version has the statement stripped out, making it really difficult to tell what is going on in the query. Looks like Statement6 is the real problem, with a very high number of reads and scans. Are there any scalar UDFs involved here?
tmt1982 2014-08-04 21:06:11
link text

Thanks Aron and Kevin I've attached the nonanonymized query

Kevin 2014-08-04 18:19:37
For starters, your estimated row counts are vastly different than your actual row counts, which means that your index statistics are grossly out of date. Run an UPDATE STATISTICS soon.

There are LOTS of clustered index scans (i.e. that's the entire table). Plus, your aggregation on table31.column5 seems to be producing a large amount of the IO in the final query.

Plus, it looks like you're joining to table4 multiple times. That might be a prime opportunity to write a multi-table join which includes transitive join. Some versions of the optimizer don't automatically know "If A=B, and B=C, than A=C". I can't see your SQL code, but many SQL devs write the A=B and B=C part of a join clause, but do not include the last subclause.

This might be a good chance to apply that technique. Of course, I'd prefer to see and comment on the real code. 🙂

-Kev

Aaron Bertrand 2014-08-05 18:35:59
This non-sargable expression:

AND CONVERT(DATETIME, CONVERT(VARCHAR(30), AA.sdtExpectedFunding, 101)) 
      < CONVERT(DATETIME, CONVERT(VARCHAR(30), GETDATE(), 101))

Should be changed to this, especially if there is an index on this column:

AND AA.sdtExpectedFunding < CONVERT(DATE, GETDATE())

Basically all of the other places where you're converting to a string and 101 – just return the data to the client or report and let that layer handle prettifying.

The query text is truncated because the cached plan doesn't contain the entire query, so there isn't much else I could see, except this column looks suspicious and inefficient:

 SELECT dbo.udfCLRConcatenateSemiColonDelimited(ORG.vchOrgName)
     FROM dbo.tsjContractBLKLegalEntity CLE
     INNER JOIN dbo.tsmOrganization ORG 
     ON CLE.intOrganizationID = ORG.intOrganizationID
     WHERE CLE.intContractID = C.intContractID

This will essentially invoke that CLR function for every single row (6,400+ invocations!). Perhaps you could give up some storage space and store those concatenations elsewhere (maintained by a trigger that calls the same CLR function, if necessary), especially if this read query happens a lot more often than each of those delimited lists would actually change. Or you could consider passing that set back separately and have the client layer perform the concatenation (C# is going to be more efficient at string concatenation that T-SQL will be at brokering that work out to C# in the CLR as part of the query).

tmt1982 2014-08-05 19:24:04
Thanks for input Aaron I'm checking what can be updated based on your input