Post reply

Bulk Insert

  • January 21, 2004 at 11:18 pm

    #107143

    We are bulk inserting data in a single table with 'bulk insert' statment. The table in which we are doing this bulk insert has got 1 clustered index on an bigint field and 6 non clustered indexes.
     

    The insertion of first 10-12 millions are done in 7-8 hrs. After that each million takes 3-4 hours. It is very clear that this difference in performance is not gradual, but very much sudden.
     
    We are not sure about the reason behind this sudden degradation in performance. We assume that this is happening because of some configuration/tweaking that needs to be done at the SQL server end.

     

    Configuration of the machine running DB server: Dual processor(2.4 GHz), 2 GB RAM, 512 HDD.

     
    What could be the possible reason for it? Do we need to do some database tweaking for it?

  • January 22, 2004 at 1:47 am

    #491074

    Is the data you are loading sorted by the clustered index already? If so, use the ORDER argument.

    If not, is it possible to drop the indexes before loading, then rebuild when loading is complete? It should be quicker to load data into the table with no indexes and then rebuild them after.

  • January 23, 2004 at 5:11 am

    #491308

    Also, I'm not sure if the Bulk Insert task automatically sets the database into Bulk-Logged recovery mode, but if not you need to do that else you are logging every insert. Check to see if your transaction logs are showing every insert.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply