Use Index or No Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Use Index or No Index

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-06-26 : 13:32:10

Hello Experts,
I am facing the clueless problem whether to use index or not in the staging tables.
I have the job which loads the Temporary Staging Tables from the Tab delimited text File.Then I do the update or insert into the base tables based on wheter the particular record exists or not.
I have about 50 columns in the staging tables.I have done some perfomance comparisions on staging tables with index and with out index.
My results seems to kind of odd.The results are better with out index.
I would love to get your advice whether to use the indexes on the temporary staging tables or just use it as heap?

I have used the clustered index on the same column for the Base Table as well as the Temp Staging Table.

Thanks in advance.

Thanks!
Raju

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 13:44:52

Clustered index or non-clustered index?

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-26 : 14:31:26

Let me ask this?

Are you going to interogate all rows in the staging table?

Most people do...so an index would only hurt the performance on the load...only. And I don't think you would see any other problems.

I guess it depends on what you are doing however. We would need more information.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-06-27 : 03:59:45

Thanks for your reply.Basically I am using the staging tables to update/insert the records into the Base Table.
I was thinking the Perfomance would improve because of the Indexes
(Comapraing the data in staging table and Base Table for updates).
Should I go ahead without the Indexes or with indexes?
Thanks in advance for the Help!
Statics without Index:
Table 'Table1'. Scan count 1, logical reads 5152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 10000, logical reads 79999, physical reads 0, read-ahead reads 0, lob logical reads 89, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 10228, physical reads 2, read-ahead reads 10216, lob logical reads 4960, lob physical reads 29, lob read-ahead reads 0.

After the Index:
Table 'Table1'. Scan count 1, logical reads 5224, physical reads 1, read-ahead reads 5221, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 10000, logical reads 79999, physical reads 0, read-ahead reads 0, lob logical reads 89, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 10227, physical reads 2, read-ahead reads 6053, lob logical reads 5060, lob physical reads 22, lob read-ahead reads 0.

Thanks!
Raju

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources