Should I index staging tables?

Question

Post reply

Should I index staging tables?

jungnaja

Old Hand

Points: 362
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: 1000539 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)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Grant Fritchey SSC Guru Points: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

jungnaja Old Hand Points: 362 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.