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?
PKColumn
the PK of the temp table? I.e. It's unique?