Post reply

Large inserts on tables with indexes

  • October 5, 2006 at 2:12 pm

    #114482

    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

    #664455

    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

    #664541

    Do you get timeouts when loading the data if you first drop the nonclustered indexes?

    Chris

  • October 6, 2006 at 8:24 am

    #664611

    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

    #664646

    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