SQL Performance Slow (Improve Insert Into Temp Table)
<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>