很慢foreach循环 [英] Very slow foreach loop
问题描述
我工作的现有应用程序。此应用程序从一个巨大的文件在另一张表中读取数据,然后,做一些计算后,其存储的数据。
但环这样做(见下文),走的是一条很长的时间。由于文件有时包含1000个记录,整个过程需要数天。
我可以更换该的foreach
循环别的东西?我试图用 Parallel.ForEach
并没有帮助。我是新来这个,所以将AP preciate你的帮助。
的foreach(记录someredord Somereport.r)
{
尝试
{
使用(VAR命令=新的SqlCommand([PROCNAME],sqlConn))
{
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(...);
IAsyncResult的结果= command.BeginExecuteReader();
而(!result.IsCompleted)
{
System.Threading.Thread.Sleep(10);
}
command.EndExecuteReader(结果);
}
}
赶上(例外五)
{
...
}
}
审查的答案之后,我删除了异步和二手编辑code如下。但这并没有提高性能。
使用(命令=新的SqlCommand([SP],sqlConn))
{
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
的foreach(记录someRecord在someReport。)
{
command.Parameters.Clear();
command.Parameters.Add(....)
。命令prepare();
使用(DR = Command.ExecuteReader却())
{
而(dr.Read())
{
如果 ()
{
}
否则,如果()
{
}
}
}
}
}
而不是循环的SQL连接了这么多次,有没有考虑过从SQL Server中提取整组数据出来,并通过数据集处理数据的?
编辑:决定进一步解释一下我的意思.. 你可以做以下,伪code如下
- 使用的select *,并从数据库中获取的所有信息,并将其存储到一个列表的类或字典的
- 请您的foreach(记录someRecord在someReport),并做好配套条件和往常一样。
I am working on an existing application. This application reads data from a huge file and then, after doing some calculations, it stores the data in another table.
But the loop doing this (see below) is taking a really long time. Since the file sometimes contains 1,000s of records, the entire process takes days.
Can I replace this foreach
loop with something else? I tried using Parallel.ForEach
and it did help. I am new to this, so will appreciate your help.
foreach (record someredord Somereport.r)
{
try
{
using (var command = new SqlCommand("[procname]", sqlConn))
{
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(…);
IAsyncResult result = command.BeginExecuteReader();
while (!result.IsCompleted)
{
System.Threading.Thread.Sleep(10);
}
command.EndExecuteReader(result);
}
}
catch (Exception e)
{
…
}
}
After reviewing the answers , I removed the Async and used edited the code as below. But this did not improve performance.
using (command = new SqlCommand("[sp]", sqlConn))
{
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
foreach (record someRecord in someReport.)
{
command.Parameters.Clear();
command.Parameters.Add(....)
command.Prepare();
using (dr = command.ExecuteReader())
{
while (dr.Read())
{
if ()
{
}
else if ()
{
}
}
}
}
}
Instead of looping the sql connection so many times, ever consider extracting the whole set of data out from sql server and process the data via the dataset?
Edit: Decided to further explain what i meant.. You can do the following, pseudo code as follow
- Use a select * and get all information from the database and store them into a list of the class or dictionary.
- Do your foreach(record someRecord in someReport) and do the condition matching as usual.
这篇关于很慢foreach循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!