October 5, 2006 at 2:12 pm
Forgive this dumb question.
But I am trying to find some documentation which explains, why bulk inserts or inserts of large amount of data > 500,000 records, may fail or timeout on a table with several (10) nonclustered indexes.
Any info is greatly appreciated.
Thanks,
Ali
October 5, 2006 at 2:48 pm
What error are you getting?
How much data in the base table before the insert?
How wide is the clustered index?
October 6, 2006 at 4:13 am
Do you get timeouts when loading the data if you first drop the nonclustered indexes?
Chris
October 6, 2006 at 8:24 am
Also of importance - exactly what is it that times out?
That is, are you running a bulk insert statement from a QA window, from some .NET app or something else?
Sometimes timeouts aren't originated from what we can see, but from someplace in between (eg a middle tier or similar)
/Kenneth
October 6, 2006 at 10:21 am
Kenneth-
I did the import using DTS through Enterprise Manager, and the server just hung.
Since the server is a production server I couldn't wait around for any disastrous affects, so I had to forcibly shut down SQL Server Enterprise Manager.
The result was that nothing was imported.
Clustered index is not wide, only clustered index is the primary key.
The rest are about 10 1-column non-clustered indexes.
Chris-
Once the identity column was removed and all the non-clustered indexes were removed, I believe I was able to run the import.
I don't recall now, because I basically just dropped the tables and recreated them without indexes, and then did a fresh import.
Thanks,
Ali
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply