Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?!

share|improve this question
1  
Best off capturing a slow and fast actual execution plan and seeing what the differences are. – Martin Smith 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

migrated from stackoverflow.com Sep 24 '12 at 13:36

1 Answer

up vote 1 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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