June 13, 2006 at 3:14 am
The table has 6 fields, 1 clustered PK and 1 unique nonclustered index. The records are copied in sequence of the clustered PK.
The table: CREATE TABLE Number (lNumberID INT NOT NULL IDENTITY(1,1), szName VARCHAR(32) NULL, lCustomerID INT NULL, dValidFrom DATETIME NULL, szArea VARCHAR(32) NULL, szInfo VARCHAR(10) NULL , CONSTRAINT PK_Number PRIMARY KEY CLUSTERED (lNumberID) )
I'm doing the copy with SQL, INSERT ... SELECT ... FROM...
Row size= 50 bytes.
The copy is done with 1000 records per batch. I'm starting with no records into the new table. The first 2090000 are copied fast (6000 per second).
But from then (after the number 2090000 is copied) it copies very slow (250 records per second).
No significant difference between first 2090000 and the next rows.
I have already tried with 10000 per batch, I have changed the index fillfactor, with and without PAD_INDEX, ... nothing changes. It keeps working slow from the number 2090000.
The only change that helps is deleting the index.
I have tried it already several times, so the data and log file does not need to grow.
After the copy I can add the index, but I want to know what happens, because adding records afterwards will also be slow.
I don't want to change the copy, I want to know: what happens after number 2090000 that slows the copy so much?
Thanks.
June 14, 2006 at 11:36 am
It is becuse of the PK cluster index, the same happen to me , my solution is always, when i want to copy alot of record drop the index and after the copy create index again. It is slow with the cluster index because he need reorder the all the record in the table to be sequencial, for each insert, if your drop the index and created it later it will do the reorder for all the rows at the same time.
June 15, 2006 at 1:35 am
June 15, 2006 at 3:32 pm
No, I don't understand that either, and I suspect that you're underestimating just how fast it would be with no indexes at all!
If you have an index (any index) on a table, the copy will be slower than without an index since SQLServer has to update the clustered index pages and the non-clustered index pages.
If the clustered index on the source table is the same as on the target table, then you may notice the biggest difference when you drop the non-clustered index only. Because the data is already sorted in clustered index sequence then you will not experience any page-splitting as you import that data. However, the clustered index pages on the target still need to be updated, and you won't get the speed that you might otherwise get.
You don't say what happens when you drop both indexes - have you tried it? If you do this I think you'll get a much better response!
June 16, 2006 at 1:44 am
The performance of the load is not the problem.
I just want to know why the difference is so much: from 6000/second to 250/second.
Is it possible that this is only because of the page splitting? And another fillfactor of the index does not change anything. On the same row, it is so much slower.
June 16, 2006 at 2:30 am
Erde
Is it possible that when you hit 2090000 rows, your data or log file fills up and has to autogrow? If it is set to grow by small increments then this will slow down the process until it finishes.
John
June 16, 2006 at 1:15 pm
Another possibility is that the process that splits the index pages as needed has consumed all available memory at that point, and has to use the swap file for completing the index reshuffle for some of the next inserts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply