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.
| 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 |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-11 : 12:19:01
|
| Hmm there's a few things here1) so i've been adding index's to various fields to test to see if i can find any improvementsOK - 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
|
|
clustered index