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?!
-
1Best off capturing a slow and fast actual execution plan and seeing what the differences are.– Martin SmithCommented Sep 21, 2012 at 19:21
-
2No offense, but who was this written by ? CASE WHEN (1=1 AND (00=11 OR 1=1 ) AND ...– astanderCommented Sep 21, 2012 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.– TrevorCommented Sep 21, 2012 at 19:35
-
Martin, I'll do that. I've taken the capture today and will get another Monday.– TrevorCommented Sep 21, 2012 at 19:51
Add a comment
|
1 Answer
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