CODEWITHSUNDEEP
sql-serverperformancet-sql
user1756190
user1756190

Reputation:

Why do I get significantly improved performances by performing the described modification in a SQL query?

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

Answers (4)

Martin Brown
Martin Brown

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

MaD
MaD

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

user1810132
user1810132

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

Keith
Keith

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

Related Questions