SQL Server Performance

expected Insert Performance

Discussion in 'SQL Server 2005 General Developer Questions' started by mgolanlan, Jan 15, 2006.

  1. mgolanlan New Member


    I've written C# code that basically does

    //open db and set Command as Sqlcommand for this connection
    //insert some records
    for(i=0 ; i<10000 ; i++) {
    Command.CommandText = "Insert into tbl1 values (" ....
    Command.ExecuteNonQuery();
    }

    obviously, this is a test. The real application gets external data and insert it into the database first-come-first-serve (ie Bulk insert is not possible) I've also tested the insert command "compiled" with the data as paramaters, and have set the Logging to Simple.

    Using Sql Express, running everything local. Log mode is "simple".
    Table has a primary key on a bigint, typical 12 fields mix.
    Modern Pentium 4 H/T 3Ghz, Mem=1Gb, disk=large modern SATA. etc.

    I'm getting 1000 to 1200 record insert per second.


    Question: is this kind of performance to be expected? I've had 10,000 inserts per second in mind as a reasonable value.

    I've also found that combining Inserts together (as a transaction) actually speed things up ... I must wonder why. I know I can do Asynch inserts but that would make the application itself far more complex.

    Would a different disk (raid, battery-backup for disk cache, etc) give different performances? using the full sql 2005 (not expressed)? another "tune up"?
  2. joechang New Member

    this is probably a reasonable value for running the client program on the same box as the server.
    this accounts for the time for sql server to perform the insert, and for the network round trip.

    if you have multiple clients performing inserts, then total throughput on sql server should be around 8K rows/sec for the 2 proc Xeon at 2.4GHz.

    for how row count inserts, it is necessary to both batch multiple rows into a single call, and within a transaction.

    batching multiple inserts into a single call shares the one-time cost of setting up a write operation.
    using a transaction to wrap the write ops reduces the number of transaction log writes, otherwise each insert in a batch must get a separate transaction log write.

    i think i have some slides on this matter in the performance articles section of this web site

Share This Page