CODEWITHSUNDEEP
c#sqlsql-server-2008-r2
k80sg
k80sg

Reputation: 2473

SQL Server + Loop insert performance

I am doing a loop insert as seen below(Method A), it seems that calling the database with every single loop isn't a good idea. I found an alternative is to loop a comma-delimited string in my SProc instead to do the insert so to have only one entry to the DB. Will be any significant improvement in terms of performance? :

Method A:

foreach (DataRow row in dt.Rows) 
{
    userBll = new UserBLL();
    UserId = (Guid)row["UserId"];
    // Call userBll method to insert into SQL Server with UserId as one of the parameter.
}

Method B: 

string UserIds = "Tom, Jerry, 007"; // Assuming we already concatenate the strings. So no loops this time here.
userBll = new UserBLL();  
// Call userBll method to insert into SQL Server with 'UserIds' as parameter.

Method B SProc / Perform a loop insert in the SProc.

if right(rtrim(@UserIds ), 1) <> ','
    SELECT @string = @UserIds + ','

SELECT @pos =  patindex('%,%' , @UserIds )

while @pos <> 0 
begin
    SELECT @piece = left(@v, (@pos-1))

    -- Perform the insert here

    SELECT @UserIds = stuff(@string, 1, @pos, '')
    SELECT @pos =  patindex('%,%' , @UserIds )
end

Upvotes: 0

Views: 1687

Answers (4)

Amy B
Amy B

Reputation: 110071

In my experience, there are three things you don't want to have to do for each record:

  1. Open/close a sql connection per row. This concern is handled by ADO.NET connection pooling. You shouldn't have to worry about it unless you have disabled the pooling.

  2. Database roundtrip per row. This tends to be less about the network bandwidth or network latency and more about the client side thread sleeping. You want a substantial amount of work on the client side each time it wakes up or you are wasting your time slice.

  3. Open/close the sql transaction log per row. Opening and closing the log is not free, but you don't want to hold it open too long either. Do many inserts in a single transaction, but not too many.

On any of these, you'll probably see a lot of improvement going from 1 row per request to 10 rows per request. You can achieve this by building up 10 insert statements on the client side before transmitting the batch.

Your approach of sending a list into a proc has been written about in extreme depth by Sommarskog.

Upvotes: 1

Michael Domashchenko
Michael Domashchenko

Reputation: 1480

You can use bulk insert functionality for this.

See this blog for details: http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

Upvotes: 0

Alex
Alex

Reputation: 23300

Less queries usually mean faster processing. That said, a co-worker of mine had some success with .NET Framework's wrapper of the TSQL BULK INSERT, which is provided by the Framework as SqlBulkCopy. This MSDN blog entry shows how to use it.

The main "API" sample is this (taken from the linked article as-is, it writes the contents of a DataTable to SQL):

private void WriteToDatabase()
{
    // get your connection string
    string connString = "";
    // connect to SQL
    using (SqlConnection connection = 
            new SqlConnection(connString))
    {
        // make sure to enable triggers
        // more on triggers in next post
        SqlBulkCopy bulkCopy = 
            new SqlBulkCopy
            (
            connection, 
            SqlBulkCopyOptions.TableLock | 
            SqlBulkCopyOptions.FireTriggers | 
            SqlBulkCopyOptions.UseInternalTransaction,
            null
            );

        // set the destination table name
        bulkCopy.DestinationTableName = this.tableName;
        connection.Open();

        // write the data in the "dataTable"
        bulkCopy.WriteToServer(dataTable);
        connection.Close();
    }
    // reset
    this.dataTable.Clear();
    this.recordCount = 0;
}

The linked article explains what needs to be done to leverage this mechanism.

Upvotes: 1

Jake H
Jake H

Reputation: 1740

If you are looking for better insert performance with multiple input values of a given type, I would recommend you look at table valued parameters.

And a sample can be found here, showing some example code that uses them.

Upvotes: 0

Related Questions