CODEWITHSUNDEEP
sql-servervb.netvisual-studiosqlbulkcopy
K.Madden
K.Madden

Reputation: 210

SQL Bulk Copy, but rebuilding index takes too long

My sql bulk copy has gotten a lot slower due to bigger database and indexes I think.

So I have a database and an app on VS where I read text file etc and transfer and "sqlbulkcopy" to my database. This was working perfect and at a fast pace but now I have over 50 million records and it has become VERY slow.

I think this is due to the fact I have indexes on my 2 tables but if I was to disable and then rebuild when done it takes way to long, e.g if just uploading 1 file it take few seconds but now takes minutes and even longer if I was to disable and rebuild index, any other suggestions? For people wondering one table takes 15 mins to rebuild all indexes while the other only takes 3.

  Dim sqlBulk As New SqlBulkCopy(con)
            'sql bulkcopy table with ticket and file path etc.
            Using sqlBulk
                'tested bulk sizes. no major effect in sizes
                sqlBulk.BatchSize = 10000
                sqlBulk.DestinationTableName = ("Test_Table")
                sqlBulk.WriteToServer(dt)
            End Using


            Using sqlBulk
                sqlBulk.BatchSize = 10000
                sqlBulk.DestinationTableName = ("Lnk_Ttks")
                sqlBulk.WriteToServer(db)
            End Using

This works but like I said it has gotten very slow now seeing as the database is huge.

Upvotes: 0

Views: 1203

Answers (1)

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

Using the TableLock options if it's possible in your scenario will increase performance.

It always depends on multiple factors but I often get 30% performance gains and more.

See:

Upvotes: 1

Related Questions