Click here to Skip to main content
15,947,389 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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);
Updated 7-Feb-13 17:21pm
itsureshuk 7-Feb-13 23:41pm    
check with stored procedure ,by sending bulk values through array to database

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.


You might be interested to investigate SQL loops:[^]
Share this answer
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

Sort of thing
Share this answer
Philippe Mori 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.
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);

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.
Share this answer
The for loop isn't the problem; it's poor use of

Use -- and reuse -- a parameterized statement.

Instantiate the command
Set the text
Add the parameters

for  ...
  Set the parameter values
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900