October 22, 2010 at 9:25 am
I am doing some benchmarks to get an idea of our performance targets.
If I have a table and I insert 1,000,000 rows and it has a few indexes on there. Then I insert another 1,000,000 - it will take longer than the first insert, for a start because it is having to update those indexes that are now even bigger in size.
If I take away any indexes - what reason would there be for additional inserts to take longer than previous ones? I have a rough inkling in my head with regards to data pages trying to be placed together to prevent fragmentation - but I need it explained to me to totally understand it. Any helpers?
My example with no indexes saw a table take 10 minutes to Insert 100,000 test records, and then a further 15 minutes to add 50,000 in!!! Much higher decrease in performance than I expected! (it is a straight test INSERT INTO with no where clauses)
October 22, 2010 at 9:27 am
Typically it's because of a clustered index and having to reorder pages when you insert new non-sequential data.
If there are no indexes at all (table is a heap), then possibly look into physical file fragmentation on the drive itself, blocking, etc.
October 22, 2010 at 1:48 pm
SSC has a massively important point. If you have a clustered index that isn't naturally sequenced in the same way that values the field arrive at the database then you will have huge page swapping overhead and it will get worse as the table gets larger.
E.d., Identity attribute PKs ARE in the natural order and will always be added at the end of the last active page in the table. Ditto for time stamp values. But if you have a table of Order_Details with the composite primary key (ProductID, OrderID) there is no natural sequencing of the ProductID value.
Also, you didn't mention any related tables but if your problem table is a child to a parent table and the parent table primary key value isn't indexed (not likely but possible) then this could cause issues.
Also, do you have any constraints or triggers on this table that require a scan of an unindexed field.
Good luck.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply