Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#4.0
how can i increase for loop performance.i have to insert 50 thousands records at high speed.but my for loop did not do this.My query is fine.its inserts 17 thosands records in 1 sec.but Problem in for loop.I am developing desktop application in c# .net 4.0.
pls provide some code that help me.Thanks in advance.

 for (int i = 1; ++i <=50000;)
               {
                 str += "insert into table1(id,memberid,nameid,id1,id2,createdby,createdon) value('" + 0 + "','" + 12 + "','" + 1 + "','" + i + "','" + 1 + "','" + "dss" + "',now());";
                   
             }
              MySqlCommand cmd = new MySqlCommand(str, con,tran);
             cmd.ExecuteNonQuery();
Posted 7-Feb-13 18:18pm
Edited 7-Feb-13 18:21pm
v3
Comments
itsureshuk at 7-Feb-13 23:41pm
   
check with stored procedure ,by sending bulk values through array to database
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Uses StringBuilder[^] to build your string. It is designed for that purpose. It will be much faster that using += operator when multiple concatenantion are required.
 
In fact, since you can estimate the size of your text, you can use the constructor that allows you to specify the capacity and it will still be more efficient as no memory allocation would occurs while building the string.
 
2 memory allocations instead of around 500000 (each + and += would cause memory allocation) will definitively make the code much more faster.
 
By the way, it might make sense to insert maybe 1000 to 10000 items at a time if you don't need them to be all added in the same statement as it would reduce memory usage and should not affect too much the performance at those sizes and your code would be able to hadle arbitrary large amount of data.
 
Alternative:
 
You might be interested to investigate SQL loops:
http://msdn.microsoft.com/en-us/library/ms178642.aspx[^]
  Permalink  
v4
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

for a start, I wouldnt use
 
str += 
 
as you have done - thats concatenating a huge string of inserts that need to be parsed out and run as seperate commands anyway
 
I would also look at (and Im not sure of the mechanics on your database since you dont say what you are using), wrapping the inserts into a transaction .. ie
 
Begin Transaction
[insert loop]
End Transaction
Commit
 
Sort of thing
  Permalink  
v2
Comments
Philippe Mori at 7-Feb-13 23:57pm
   
If run locally, it might give similar performance but if the database is remote, then each query would have overhead. When a database is remote, you want to reduce the number of queries as much as possible.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

I would suggest following change to start with:
 
 StringBuilder sb = new StringBuilder();
 
for (int i = 1; ++i <= 50000; )
{
   sb.Append(string.Format("insert into table1(id,memberid,nameid,id1,id2,createdby,createdon) value('{0}','{1}','{2}','{3}','{4}','dss',now());", 0, 12, 1, i, 1));
 
 }
MySqlCommand cmd = new MySqlCommand(sb.ToString(), con, tran);
cmd.ExecuteNonQuery();
 
The above code has not optimized the loop itself but really saved up a lot of memory and resources by utilizing the stringbuilder and removing on the fly string concatenations. Since you are running the loop 50000 times and the string itself if quite lengthy, you will see a VERY BIG performance gain.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

The for loop isn't the problem; it's poor use of ADO.net.
 
Use -- and reuse -- a parameterized statement.
 
Instantiate the command
Set the text
Add the parameters
 
for  ...
{
  Set the parameter values
  Execute
}
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 519
2 Maciej Los 315
3 Mathew Soji 195
4 BillWoodruff 180
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,712
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100