index creation on temp tables

Question

index creation on temp tables

Lexa

Hall of Fame

Points: 3314
More actions
December 20, 2012 at 2:17 pm

#268365

Are there situations where creation of an index on a temp table should be done at the time of table creation and NOT after the insert operation? If there are more than one index on a temp table, when does it matter if the index is a clustered index or a non-clustered?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 1

If your Temp Tables are large enough to warrant indexes, you might be putting too much in your Temp Tables which defeats the whole purpose that you're trying to accomplish... Divide'n'Conquer.

To answer your questions... "It Depends". If you have the need for speed, sometimes doing a SELECT/INTO followed by adding indexes is faster. Sometimes, pre-creating the table with indexes is faster. You have to test to know. Sometimes, having indexes on the Temp Table will actually slow things down especially if you've captured in the Temp Table only the things you need. In other words, test that, as well, because Indexes are not a performance panacea... not by a long shot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lexa Hall of Fame Points: 3314 More actions · Answer 2

Jeff Moden (12/20/2012)
If your Temp Tables are large enough to warrant indexes, you might be putting too much in your Temp Tables which defeats the whole purpose that you're trying to accomplish... Divide'n'Conquer.
To answer your questions... "It Depends". If you have the need for speed, sometimes doing a SELECT/INTO followed by adding indexes is faster. Sometimes, pre-creating the table with indexes is faster. You have to test to know. Sometimes, having indexes on the Temp Table will actually slow things down especially if you've captured in the Temp Table only the things you need. In other words, test that, as well, because Indexes are not a performance panacea... not by a long shot.

Thanks. Of course I will test these scenarios but a follow up question:

create table #t1 (f1 int, f2, int, f3 varchar(300) ~ table gets loaded with 2k-500k based on a set of parameters.

then the following happens:

delete #t1 from #t1 t join #another_table s on t.f1 = s.f1

insert #third_table

select t.f1

from #t1 t join fourth_table f on t.f2 = f.f2

Is it better to create 2 indexes for f1 and f2 or one composite?

Should there be a clustered index?

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 3

Lexa (12/20/2012)
Jeff Moden (12/20/2012)
If your Temp Tables are large enough to warrant indexes, you might be putting too much in your Temp Tables which defeats the whole purpose that you're trying to accomplish... Divide'n'Conquer.
To answer your questions... "It Depends". If you have the need for speed, sometimes doing a SELECT/INTO followed by adding indexes is faster. Sometimes, pre-creating the table with indexes is faster. You have to test to know. Sometimes, having indexes on the Temp Table will actually slow things down especially if you've captured in the Temp Table only the things you need. In other words, test that, as well, because Indexes are not a performance panacea... not by a long shot.
Thanks. Of course I will test these scenarios but a follow up question:
create table #t1 (f1 int, f2, int, f3 varchar(300) ~ table gets loaded with 2k-500k based on a set of parameters.
then the following happens:
delete #t1 from #t1 t join #another_table s on t.f1 = s.f1
insert #third_table
select t.f1
from #t1 t join fourth_table f on t.f2 = f.f2
Is it better to create 2 indexes for f1 and f2 or one composite?
Should there be a clustered index?

Maybe neither. Having only the data you actually need in the table frequently eliminates the need for any indexing simply because you're going to use all of the data in the table. There are times when a TABLE SCAN is much more effective than using any kind of index especially when all of the rows in the table come into play.

As a side bar, you've just exemplified the problem I was speaking of by DELETEing from a TempTable. Temp Tables start out in memory just like Table Variables do. Both spill to disk if they use too much memory. Obviously, keeping something like that in memory is going to be a whole lot faster. You stand a much better chance of keeping such data in memory if you only load what is needed instead of loading stuff and deleting it (total waste of memory and clock cycles). Those rows should be prevented from ever entering the Temp Table.

If the Temp Table does fit in memory, adding an index to it can be the straw that breaks the proverbial camel's back and causes the table to leave memory and spool to disk where the change in performance would greatly outweigh the benefit of having an index.

To really answer your question and if forced to keep the status quo of the current code, I'd put the clustered index on f2 and a non-clustered index on f1 as a first blush guess and then test like hell to see if any other combinations were faster. The optimizer can be full of both pleasant and horrible surprises.

I'd also test to see if it would be faster to bypass using a Temp Table altogether. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)