如何以100 * 10批次获取千条记录 [英] How to fetch thousand records in 100*10 batches

问题描述

嗨朋友们，



我需要从SQL Db中提取数据并进行一些计算。我不想一次性获取所有记录。我的要求是批量运行循环。

例如，假设我有1050条记​​录。它应该运行11个循环，即10 * 100 + 50



我应该遵循哪种方法。我应该写一个存储过程。如果有，请给我一些建议。



下面是我的代码快照

 SqlDataAdapter daAuthors =  new  SqlDataAdapter（ 选择*来自作者，objConn）; 
 DataSet dsPubs =  new  DataSet（  Pubs ）; 
 daAuthors.Fill（dsPubs， 作者）; 
 
 DataTable tblAuthors; 
 tblAuthors = dsPubs.Tables [ 作者]; 
 
  foreach （DataRow drCurrent  in  tblAuthors.Rows）
 { 
 Console.WriteLine（  {0} {1}，
 drCurrent [  au_fname]。ToString（），
 drCurrent [  au_lname]。ToString（））; 
} 

解决方案

我建​​议第三个选项：一次查询所有记录，但不要使用DataAdapter .Fill - 而是使用DataReader逐行读取。这样你就不必处理select-paging，同时仍然不必一次读取所有记录。它甚至可以获得最佳性能（只有一个查询并使用DataReader比DataAdapter.Fill更快）。



SqlDataReader（MSDN） [ ^ ]

http://www.dotnetperls.com/sqldatareader [ ^ ]



编辑：它会有如果计算由于某种原因在应用程序中完成，则表现最佳。如果它可以在查询（CTE）中完成，那肯定是最快的方式。但我们无法分辨，因为我们不知道你的计算涉及（还）。



编辑后评论：在那case（处理数百万条记录并且必须将结果与DB2数据库进行比较）批处理似乎确实是最好的方法。当您使用SQL Server 2008时，还没有OFFSET FETCH子句，因此您必须使用一些常规分页方法。我认为这篇文章很好地解释了您的选择：

SQL Server 2005分页结果 [ ^ ]

否则还有更多需要关注的内容：

https://www.google.com/search?q=codeproject+sql+server+select+paging&ie=utf-8&oe = utf-8 [ ^ ]



尽管如此，您可以使用DataReader而不是DataAdapter.Fill来提高性能有些

Hi Friends,

I need to pull the data from the SQL Db and do some calculations. I am not interested to fetch all the records in a one shot. My requirement is to run the loop in batches.
For example if suppose i have 1050 records. it should run 11 loops ie 10*100+ 50

Whats the method should i follow. Should i write a stored procedure. If yes please give me some idea.

Below i my snapshot of the code

SqlDataAdapter daAuthors = new SqlDataAdapter("Select * From Authors", objConn);
DataSet dsPubs = new DataSet("Pubs");
daAuthors.Fill(dsPubs,"Authors");

DataTable tblAuthors;
tblAuthors = dsPubs.Tables["Authors"];

			foreach (DataRow drCurrent in tblAuthors.Rows)
			{
				Console.WriteLine("{0} {1}",
					drCurrent["au_fname"].ToString(),
					drCurrent["au_lname"].ToString());
			}

解决方案

I'd suggest a third option: Query for all records at once, but don't use DataAdapter.Fill - instead use a DataReader to read row by row. That way you don't have to deal with select-paging while still not having to read all records at once. It will even have the best performance (only one query and using a DataReader is faster than DataAdapter.Fill).

SqlDataReader (MSDN)[^]
http://www.dotnetperls.com/sqldatareader[^]

edit: It will have the best performance if the calculation has, for some reason, to be done in the application. If it could be done within the query (CTE) that would most certainly be the fastest way. But we can't tell, because we don't know what your calculation involves (yet).

edit2 after comment: In that case (processing millions of records and having to compare the results to a DB2 database) batching seems indeed to be the best approach. As you're using SQL Server 2008, there's no OFFSET FETCH-clause yet, so you will have to use some some 'conventional' paging approach. I think this article explains your options well:
SQL Server 2005 Paging Results[^]
Otherwise there are even more to take a look at:
https://www.google.com/search?q=codeproject+sql+server+select+paging&ie=utf-8&oe=utf-8[^]

Nonetheless, you could use a DataReader instead of DataAdapter.Fill to improve the performance somewhat.

这篇关于如何以100 * 10批次获取千条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！