5

I have a stored procedure that looks something like this:

SELECT columnlist
INTO #temptable
FROM Table
JOIN lotsofothertables
WHERE severalconditions

UPDATE anothertable
SET column = #temptable.column
FROM anothertable 
JOIN #temptable
    ON anothertable.PKColumn = #temptable.PKColumn

The initial query will frequently produce just a few columns (<100) so the update is fast. But occasionally (and these are the runs we are having problems with) it produces 1000's or even 10,000's. Would it be reasonable to add something like this between them?

IF @@RowCount > 100
   CREATE INDEX ix_temp ON #temptable(PKColumn)

Or am I better off just creating the index on the table regardless since with so few rows the amount of time spent creating the index will be fairly small?

3
  • Is PKColumn the PK of the temp table? I.e. It's unique? Apr 29, 2016 at 20:55
  • Lets assume that it is. Apr 29, 2016 at 20:56
  • 3
    I would just always create the index (but inline as Martin described). Apr 29, 2016 at 22:04

1 Answer 1

8

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged).

In this case if you were to create the temp table with an unnamed primary key constraint on the column up front

CREATE TABLE #temptable
(
PKColumn INT PRIMARY KEY
)

rather than creating the table and adding an index afterwards you would be able to benefit (at least potentially as that does have some issues) from temp table object caching.

I wouldn't bother making it conditional on row count unless you've proved that the presence of the index with few rows somehow makes a significant deterioration in performance (unlikely).

1
  • 3
    Personally, I've never found putting a PK/index on a temp table to be a noticeable, detrimental difference when the table was small. However, it always helps when the table ends up being bigger. Apr 29, 2016 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.