SQL Performance Slow (Improve Insert Into Temp Table)

Question

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.

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:

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.

SQL code

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.

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 >= @StartDate AND Audit_Date < DATEADD(dd,1,@EndDate)
            --WHERE ((SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Audit_Date))) >= @StartDate 
             -- AND  (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Audit_Date))) <= @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

Execution Plan:

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?

Answer 1

I guess there could be few different causes of the problem. At least, the assumption is that because of the big number of fields in one record can cause Page overflow in Temp Heap table. Along with that there might be contention in tempdb or even it's slowness. So, the general suggestions might be:
1. As already suggested, try to do not use temp table at all.
2. If possible, try to limit record size to fit into one page. Or even better, if you can fit 2-3 records into one page.
3. If it is possible, use "staging" table with clustered index on it, instead of temp table. Do not truncate that table, only do deletes.
4. If using temp table: create table before the insert with clustered index on it.
5. Fallow Paul Randal's suggestions about TempDB: http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/

For deeper troubleshooting, I'd suggest, during the execution of that query, to capture waits, locks, I/O, memory and CPU activity.