SQL Server Performance Forum – Threads Archive
A lot of inserts
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, ChrisAre 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.
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.
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
]]>