Click here to Skip to main content
11,811,971 members (54,885 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: C# 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);
Posted 7-Feb-13 17:18pm
Edited 7-Feb-13 17:21pm
itsureshuk at 7-Feb-13 23:41pm
check with stored procedure ,by sending bulk values through array to database
Rate this: bad
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.


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

Sort of thing
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
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);

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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 6

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

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

  Print Answers RSS
0 KrunalRohit 354
1 Palash Mondal_ 185
2 CHill60 180
3 OriginalGriff 178
4 Abhinav S 175
0 OriginalGriff 3,888
1 KrunalRohit 2,392
2 CPallini 2,240
3 Maciej Los 2,155
4 ppolymorphe 1,725

Advertise | Privacy | Mobile
Web01 | 2.8.151002.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2015
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