CODEWITHSUNDEEP
vb.netmultithreadingsqlclient
surpavan
surpavan

Reputation: 1422

Multi threading SQL Client Write queries

I have a MS sql database (not server), my program reads file structure from my 2 TB external drive and saves the list a records in database. I write around 80k records every time and it takes a great time.

I am currently writing records by dividing them into 2 background Worker thread 40k each. My pc is Quad core Phenom II.

What is the best way to achieve this and also would there be any benefits by increasing the number of threads because mine is only a 7200rpm hdd and not raid. What I mean is HDD might be a limitation?

Thank you.

Edit: Time is write all records is around 15-18 mins.I am not using stored procedures. I am using the insert command from the auto generated table adapters only. I am looping the insert statement 40k times for each thread. No, not updating based on indexed file, they are just insert statements.

Upvotes: 0

Views: 1792

Answers (2)

competent_tech
competent_tech

Reputation: 44941

I have a couple of suggestions:

1) Ensure that both the database and the log file for the database that you are writing to have more than enough free space to accommodate the data that you are writing. Dynamically resizing databases is a very expensive operation for SQL Server and if you start with a small database that has a small resize percentage, you will be continuously resizing it.

2) Wrap your insert commands in a transaction. This should substantially improve the speed. You probably won't be able to wrap all 80k records in a single transaction, but you could try 1000 or so at a time. Pseudo-code:

    Const MAX_RECORDS_PER_LOOP = 1000
    Dim Counter As Integer
    Dim trans As SqlTransaction

    Try
        For Each record In File
            ' If we are not in a transaction, enter one
            If trans Is Nothing Then
                trans = conn.BeginTransaction()
            End If

            ' Do your insert

            ' Boost the counter
            Counter += 1

            ' We have reached the max
            If Counter = MAX_RECORDS_PER_LOOP Then
                ' Commit the previous batch and reset the values
                trans.Commit()
                trans = Nothing
                Counter = 0
            End If
        Next

        ' Commit the last batch, if any
        If trans IsNot Nothing Then
            trans.Commit()
        End If

    Catch theException As Exception
        ' Report the exception
        ' Rollback if trans in progress
        If trans IsNot Nothing Then
            trans.Rollback()
        End If
    End Try

Upvotes: 1

Jared Peless
Jared Peless

Reputation: 1120

80k records shouldn't take a ton of time. You say a "great time" but what is it actually? We have no idea of your relative term's actual time.

1) are you using stored procedures? 2) Is it something that can be handled in bulk (BCP or with many insert/update statements generated and sent in a single command)? 3) Are all updates using indexed fields in the WHERE clause?

Upvotes: 2

Related Questions