Temp table loading in a proc

Question

Post reply

Temp table loading in a proc

Lexa

Hall of Fame

Points: 3314
More actions
October 18, 2011 at 4:29 pm

#246673

Hi,
I'm reviewing a few cases where temp tables get created, loaded with about 100K records, then have an index created on those tables for farther manipulation.
If the index is not specified as clustered ex. "create index my_index on #my_table(f1, f2, f3)" and the fields are used in a join, would it make sense to
1. build it as a clustered index
2. build it before loading the table
???
Thanks

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Actually the first test would be to see if removing it helps.

Step 2 would be to see where and how that table is used.

Most of the time when you use intermediary table you need to touch all the rows so indexing here rarely helps.

Stats on the other hand could really help build the correct execution plan later on when you use that table.

The only way to tell is to see the actual execution plan.

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

Ninja's_RGR'us (10/18/2011)
Actually the first test would be to see if removing it helps.
Step 2 would be to see where and how that table is used.
Most of the time when you use intermediary table you need to touch all the rows so indexing here rarely helps.
Stats on the other hand could really help build the correct execution plan later on when you use that table.
The only way to tell is to see the actual execution plan.

So basically because it's just 100K records everything will be loaded into memory?

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

Lexa (10/18/2011)
Ninja's_RGR'us (10/18/2011)
Actually the first test would be to see if removing it helps.
Step 2 would be to see where and how that table is used.
Most of the time when you use intermediary table you need to touch all the rows so indexing here rarely helps.
Stats on the other hand could really help build the correct execution plan later on when you use that table.
The only way to tell is to see the actual execution plan.
So basically because it's just 100K records everything will be loaded into memory?

I'm not sure how you came to that conclusion from what was posted but nope... it depends. If there's room for it, then maybe.

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

No because indexing 100K rows takes a whole lot of ressources.

Quite often the index is useless, because you scan the whole temp table when joining to something else, apart from the stats that are derived from it which are usefull to the optimizer to guess the load.