April 3, 2002 at 5:48 pm
The table where I am inserting has a ID field(which is a identity column and is a unique non-clustered index). Also there is another field Usercode, defined as clustered index.
The problem occurs when a user is trying to do a massive insert of about 10,000 rows into that table, for a particular user code.
The insert slows down considerably, when the index on user code is clustered
Changing the index on usercode to non-clustered, speeds up the performance.
Though i know the solution, I cannot understand the behaviour.
Please advice me on your suggestions. ANy thoughts are welcome...
April 3, 2002 at 7:09 pm
This is a duplicate post. Please see other thread at http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=3399&FORUM_ID=9&CAT_ID=1&Topic_Title=Insert++slows+down+due+to+Clustered+Index&Forum_Title=General for posting.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 22, 2002 at 7:53 am
The clusterd order is the order in whichthe files are on disk.... So if you are inserting a lot of records for a specific user all the data that is after that user has to shift with every insert. If you delete the clustered index the records are just appended to the end. So the data doesn't have to be moved physically on disk....... That makes all tth difference
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply