title: SQL Performance Slow (Improve Insert Into Temp Table)
body: <p>I have been working on an old auditing stored procedure that is running slowly, I have had some success from applying indexing and making the query more sargable.</p>
  
  <p>However the stored procedure is still taking over one minute to complete. I think the problem lays in the temp table insert. I did try to apply an index to the temp table but this will only reduce performance as: </p>
  
  <blockquote>
    <p>The number of indexes on a table is the most dominant factor for
    insert performance. The more indexes a table has, the slower the
    execution becomes. The insert statement is the only operation that
    cannot directly benefit from indexing because it has no where clause.</p>
  </blockquote>
  
  <p><strong>SQL code</strong></p>
  
  <p>I have posted below the snippet of code from the auditing procedure that is taking the longest time to process and included the execution plan. </p>
  
  <pre><code>SELECT dbo.[Audit Result Entry Detail].PK_ID,
    dbo.[Audit Result Entry Detail]....... 
     45-50 other columns selected from Audit Result Entry Detail 
     (Note i need to select all these)
     dbo.[Audit Register].Audit_Date,
     dbo.[Audit Register].Audit_Type,
     dbo.[Audit Register].ContextUser
   INTO #temp5
  
   FROM dbo.[Audit Result Entry Detail]
     INNER 
      JOIN dbo.[Audit Register]
     ON dbo.[Audit Result Entry Detail].FK_RegisterID = dbo.[Audit Register].PK_ID
     INNER 
      JOIN (
       SELECT MAX(Audit_Date) AS DATE,
           FK_RegisterID
         FROM dbo.[Audit Result Entry Detail]
        INNER 
         JOIN dbo.[Audit Register]
        ON dbo.[Audit Result Entry Detail].FK_RegisterID = dbo.[Audit Register].PK_ID
     WHERE Audit_Date &gt;= @StartDate AND Audit_Date &lt; DATEADD(dd,1,@EndDate)
              --WHERE ((SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Audit_Date))) &gt;= @StartDate 
               -- AND  (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Audit_Date))) &lt;= @EndDate)
                AND part_number = @ParticipantNumber
        GROUP 
           BY FK_RegisterID
     ) dt
     ON dbo.[Audit Result Entry Detail].FK_RegisterID = dt.FK_RegisterID
    AND dbo.[Audit Register].Audit_Date = dt.[date]
    WHERE part_number = @ParticipantNumber
  </code></pre>
  
  <p><strong>Execution Plan:</strong>
  <a href="https://i.stack.imgur.com/oxXKq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oxXKq.png" alt="Execution_Plan"></a></p>
  
  <p>I believe the bottleneck is the #temp5 table, my question is there a way I can speed up the insert into the temp table or is there a better alternative to a temp table?</p>