SQL Server Performance

A lot of inserts

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by chris_dk, Sep 7, 2005.

  1. chris_dk New Member

    Hi,

    I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server 2000. I have tried to use either DataTable/DataAdapter, stored procedures and plain SQL to do the inserts. I have encapsulated everything in a transaction because I found that the performance was better.

    However, I can only do 10000 inserts in about 9 seconds on Pentium III 1 GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored procedure etc.). Can I get better performance using another technique?

    I really want to do about 10000 inserts in 0.01 seconds which my binary files can handle on the same machine. Is this possible?

    Why is the performance higher when using transactions?

    Best regards,

    Chris
  2. satya Moderator

    Are you performing the insert in batches, if so it will have more performance.
    Adding more physical RAM will definetly help to address the resource crunch.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. chris_dk New Member

    quote:Originally posted by satya

    Are you performing the insert in batches, if so it will have more performance.
    Adding more physical RAM will definetly help to address the resource crunch.

    Well, I've tried to use batches (DataAdapter.UpdateBatchSize). I can get it down to 7 seconds using 100 as the batch size.

    Are there any options/switches which helps boosting performance?

    I will try to see if adding RAM helps.

    Thanks.

    /Chris

Share This Page