Should I index staging tables?

Question

Post reply

Should I index staging tables?

jungnaja

SSC-Addicted

Points: 422
More actions
December 14, 2008 at 5:55 pm

#202894

I have a few staging tables. They are highly active tables with a lot of inserts and deletes. Should I index them? The auto statistic is on.

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

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

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

Impossible to tell from what you've described.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Well, I guess the answer is: It depends :hehe:

Do you want to slow down your inserts/deletes? If so, then yeah - you probably want to add some indexes. Do you want to speed up your selects without slowing down your inserts/deletes - then, maybe, but probably not. And finally, do you want to speed up your deletes at the expense of your inserts? If so, then yeah - you can probably add some indexes that would help.

Most of the time, you don't index staging tables at all. Validation and verification of the data is done after loading the staging tables, just prior to loading into the production tables. And since you want to load the staging tables as fast as possible - you really don't want to add any indexes.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Are the inserts & deletes done all the time or in discrete batches? If the latter, you can leave indexes off until you need them and then add them. If it's constant, you'll have to weigh the cost/benefit as Jeffrey outlined.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jungnaja SSC-Addicted Points: 422 More actions · Answer 4

Thank you everyone for the responses. There are jobs checking these staging tables every 15 minutes to get the data to insert into the fact tables. So the tables are constantly changing.