Reputation:
I was trying to modify a select query, which is included in a SP (TSQL), in order to speed up this SP. The original run time of this query was a few minutes and splitting this query into two parts, improved the run time to a few seconds only. No change was done to the defined index, and the same one is still used for this query. Can someone please explain what exactly caused this significant improvement and why?
Thank you.
The original query:
SELECT ( AgentFirstName + ' ' + AgentLastName ) AS AgentName,
AC.Agent_ID,
AC.TimeStamp
INTO #tAgentList
FROM AgentConfiguration AC
JOIN Getpermittedagents(@tenantId, @userName) AS PA
ON AC.Agent_ID = PA.Agent_ID
OR PA.Agent_ID = -1
WHERE AC.TimeStamp < @To
AND AC.Tenant_ID = @tenantId
AND ( EXISTS (SELECT *
FROM AgentsCampaignActivities AS ACA
WHERE AC.AgentGlobal_ID = ACA.AgentGlobal_ID)
OR @IsCampaignReport = 0 )
The improved query:
SELECT Agent_ID,
AgentFirstName,
AgentLastName,
TimeStamp
INTO #tt
FROM AgentConfiguration
WHERE TimeStamp > @From
AND TimeStamp < @To
AND Tenant_ID = @tenantId
AND ( EXISTS (SELECT *
FROM AgentsCampaignActivities AS ACA
WHERE AgentGlobal_ID = ACA.AgentGlobal_ID)
OR @IsCampaignReport = 0 )
SELECT ( AgentFirstName + ' ' + AgentLastName ) AS AgentName,
tt.Agent_ID,
tt.TimeStamp
INTO #tAgentList
FROM Getpermittedagents(@tenantId, @userName) AS PA
JOIN #tt tt
ON tt.Agent_ID = PA.Agent_ID
OR PA.Agent_ID = -1
Upvotes: 4
Views: 145
Reputation: 25329
It looks to me like the two queries presented are not equivalent. In the second you are filtering where TimeStamp > @From where as the first query does not do this. As such I would guess that the first query is dealing with more rows than the second.
Upvotes: 1
Reputation: 106
This is a hypotesis only, which could be confirmed by comparing execution plans, but the key difference is probably in this clause: ON AC.Agent_ID = PA.Agent_ID OR PA.Agent_ID = -1
this probably busts using index on PA.Agent_ID.
In first case it's executed on all data, in the second one on pre-filtered set.
Upvotes: 1
Reputation: 61
Try this.. use only required or indexed auto increment column name in EXISTS section. It reduces READ data from a table. "EXISTS (SELECT *"
Please Post both execution plans for more.
Upvotes: 0
Reputation: 21264
The join between AgentConfiguration
and Getpermittedagents
is apparently expensive. My guess is that AgentConfiguration
needs an index on Agent_ID
but it's hard to tell without an execution plan. I recommend you study the execution plan for both queries.
Upvotes: 0