如何增加循环性能 [英] How to increase for loop performance

问题描述

如何增加循环性能。我必须高速插入50千条记录。但我的for循环没有这样做。我的查询很好。在1秒内插入17个thosands记录。但问题我正在开发c＃.net 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();

推荐答案

使用 StringBuilder [ ^ ]构建你的字符串。它是为此目的而设计的。当需要多个连接时，使用+ =运算符要快得多。



事实上，既然你可以估计文本的大小，你可以使用构造函数这允许你指定容量，它仍然会更高效，因为在构建字符串时不会发生内存分配。



2内存分配而不是大约500000（每个+和+ =会导致内存分配）最终会使代码更加快速。



顺便说一下，插入1000到10000个项目可能是有意义的如果你不需要将它们全部添加到相同的语句中，因为它会减少内存使用量，并且不应该影响那些大小的性能，并且你的代码将能够处理任意大量的数据。



替代方案：



您可能有兴趣研究SQL循环：

http://msdn.microsoft.com/en-us/library/ms178642.aspx [ ^ ]

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

一开始，我不会用

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

我建​​议关注更改为开始于：

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();

上面的代码没有对循环本身进行优化，但确实节省了大量内存利用 stringbuilder 并删除快速字符串连接。由于您运行循环50000次并且字符串本身非常冗长，因此您将看到非常大的性能提升。

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.

这篇关于如何增加循环性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！