2

SQL Server 2008 R2. We are reporting off a DB-Amp backup of our SalesForce instance.
I have a SQL query that takes about 8 seconds to run on Monday. By Friday the duration is 31 seconds and my app is giving up a second too early. By the next Monday, the query is performing quickly again.
Here is the SQL
I don't have any Jobs running weekly that do any cleanup. Rebuilding indexes and sp_syspolicy_purge_history are nightly jobs.
I checked Profiler and nothing else is happening while the query is running slowly.
I don't see any User Temp tables that were created before today (Friday).
I have another similar query that never slows down.
Any ideas?!

4
  • 1
    Best off capturing a slow and fast actual execution plan and seeing what the differences are. Sep 21 '12 at 19:21
  • 2
    No offense, but who was this written by ? CASE WHEN (1=1 AND (00=11 OR 1=1 ) AND ...
    – astander
    Sep 21 '12 at 19:21
  • The SQL is actually modified quite a bit by my application. Those are place holders for string substitution or so I don't have to add logic to decide if I should remove the first "AND". This specific SQL is always the primary execution from the first viewing.
    – Trevor
    Sep 21 '12 at 19:35
  • Martin, I'll do that. I've taken the capture today and will get another Monday.
    – Trevor
    Sep 21 '12 at 19:51
1

I didn't see the speedup this weekend?! However, I did find the AccountTeamRollup CTE was a huge drain.
I was able to remove the CTE and replace the referring 'Team' column with the below. It went from 30sec to 4sec.

,STUFF(
    (SELECT ',' + SUB.UserN AS [text()]
    FROM (
        Select distinct atm.AccountId, u.Name UserN
        From AccountTeamMember atm (nolock) 
            INNER JOIN [User] u  (nolock) 
                ON atm.UserId = u.Id) SUB
    WHERE SUB.AccountId = acct.Id
    FOR XML PATH('') ), 1, 1, '' ) Team

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.