April 3, 2002 at 5:54 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:05 pm
The problem isn't the clustered index. The problem is the pages filling up. When this occurrs page splits occurr and data has to be moved to make room. Thus if a large insert occurrs plus you already have a large amount of data and the data will insert into the table anywhere throughout the table then when a page fills and another row is inserted (think of each row as actually a new insert) then the last record will get moved to the next page. Now if that page is full then it's last record will move, and so on. Then your next record is inserted, and if it is not at the end or on a page with space left the whole process happens again. This is one of the major disadvantages of clustered indexes. The are great and have lots of bennifits but you have to keep in mind how the data will affect the whole table and if page splits will occurr then you may need to just make a unique non-clustered index or rebuild the clustered index with a fill factor to provide room to limit page splits.
Hope this all makes sense and I didn't ramble too much.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply