Why do I get significantly improved performance by performing the described modification in an SQL query?

Question

I tried to modify the select query that is included in SP (TSQL) to speed up this SP. The initial execution time of this request was several minutes and divided this request into two parts, improving the run time by only a few seconds. No changes were made to the specified index, and is still used for this query. Can someone explain what exactly caused this significant improvement and why?

Thanks.

Original request:

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 ) 

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 

Answer 1

This is only a hypothesis that can be confirmed by comparing execution plans, but the key difference is probably in this section: ON AC.Agent_ID = PA.Agent_ID OR PA.Agent_ID = -1

this probably happens using the index on PA.Agent_ID.

In the first case, it is executed in all data, in the second - in a pre-filtered set.

Answer 2

It seems to me that the two queries presented are not equivalent. In the second, you filter where TimeStamp> @From, where the first request does not. Therefore, I assume that the first query deals with more rows than the second.

Answer 3

The connection between AgentConfiguration and Getpermittedagents seems to be expensive. I assume that AgentConfiguration needs an index on Agent_ID , but it's hard to say without an execution plan. I recommend that you study the execution plan for both queries.

Answer 4

Try it. use only the required or indexed auto-increment column name in the EXISTS section. This reduces the READ data from the table. "EXISTS (SELECT *"

Please submit both execution plans for more.