How to increase for loop performance

Question

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.

C#

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 17:18pm

pankajgarg1986

Updated 7-Feb-13 17:21pm

v3

Add a Solution

Comments

itsureshuk 7-Feb-13 23:41pm

check with stored procedure ,by sending bulk values through array to database

4 solutions

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[^]

Posted 7-Feb-13 17:44pm

Philippe Mori

Updated 7-Feb-13 17:58pm

v4

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

Posted 7-Feb-13 17:44pm

Garth J Lancaster

Updated 7-Feb-13 17:47pm

v2

Comments

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.

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
}

Posted 7-Feb-13 17:50pm

PIEBALDconsult

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Rahul Rajat Singh · Accepted Answer · 2013-02-07T17:47:00

I would suggest following change to start with:

C#

 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.