title: Why do I get significantly improved performances by performing the described modification in a SQL query?
body: <p>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? </p>
  
  <p>Thank you. </p>
  
  <p>The original query: </p>
  
  <pre><code>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 &lt; @To
         AND AC.Tenant_ID = @tenantId
         AND ( EXISTS (SELECT *
                       FROM   AgentsCampaignActivities AS ACA
                       WHERE  AC.AgentGlobal_ID = ACA.AgentGlobal_ID)
                OR @IsCampaignReport = 0 ) 
  </code></pre>
  
  <p>The improved query: </p>
  
  <pre><code>SELECT Agent_ID,
         AgentFirstName,
         AgentLastName,
         TimeStamp
  INTO   #tt
  FROM   AgentConfiguration
  WHERE  TimeStamp &gt; @From
         AND TimeStamp &lt; @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 
  </code></pre>