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
 General SQL Server Forums
 Database Design and Application Architecture
 clustered index

Author  Topic 

backwardselnino
Starting Member

1 Post

Posted - 2010-10-11 : 11:39:16
hi,

i have a query which is running slow so i've been adding index's to various fields to test to see if i can find any improvements.

i have a problem though as a program runs and instead of updating values deletes a record the by the id field and then re-adds with the new values (yes stupid i know).

what i've noticed is that there is an index on that field set to 'create as clustered'.

I've always avoided the clustered index as i've heard its a bad option, but am wondering that if this value is being deleted and re-created and is set to the clustered mode if this might be a problem?

sql 2000.

thanks

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-10-11 : 11:53:32
A clustered index is not a "bad" option, if well chosen it can be the best option.

Often in these cases the query is the issue. Can you provide the query, some sample data and expected output?

-----------

I used to think I wasn't a morning person but it never got better in the afternoon
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-11 : 12:19:01
Hmm there's a few things here

1) so i've been adding index's to various fields to test to see if i can find any improvements

OK - Doesn't sound like you have a good idea of which fields / where an idnex can best help out the queries. I'd be worried about ending up with a ton of indexes which don't get used (Because there is a better more selective index) and / or create performance problems when inserting an updating.

2) I've always avoided the clustered index as i've heard its a bad option, but am wondering that if this value is being deleted and re-created and is set to the clustered mode if this might be a problem?

Erm. Clustered index is the "structure" of your data. This is why you can only have one of them. In general (read in 99% of situations) is's desirable to have a clustered index on the table, otherwise the table is a heap. However......

3) i have a problem though as a program runs and instead of updating values deletes a record the by the id field and then re-adds with the new values (yes stupid i know).

URGH! Yeah, you are right, that's kinda.... unnecessary. INSERTING into clustered index when you are not inserting a new highest value / set of values is time consuming (it has to shuffle around all your data). Is this something you can change?


But yeah -- post the query. We may be able to make suggestions / show you a better more performant way of doing it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-11 : 12:20:57
All SQL tables should (in my opinion) have a clustered index.

This may be worth a read: http://www.sqlservercentral.com/articles/Indexing/68563/

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -