什么是获取记录分批从SQL Server的最佳方式 [英] What is the best way to fetch records batch-wise from SQL Server

问题描述

场景：我们取从SQL Server行C＃.NET控制台应用程序，并在执行从SQL Server的通过存储过程检索到的数据的动作;执行该操作后的新数据是用C＃-MongoDB驱动器存储到MongoDB的

问题：有几十亿的行。我的存储过程包含查询，如下所示：

  SELECT * FROM table_name的
  

要制定出一些间歇逻辑没有标识列，也没有任何日期列或这样的。

资讯：截至目前应用程序获取记录高达3500 - 5000条记录，并存储到MongoDB的，然后如下抛出一个错误是：

System.Runtime.InteropServices.SEHException（0X80004005）：外部组件发生异常。

问：任何人都可以建议我一些逻辑来制定出间歇读/从SQL取服务器？

解决方案

如果您不能使用 OFFSET取中SQL Server 2012中，并假设该表有一个主键或允许您唯一标识行的列（S），让我们把它叫做设置唯一，然后在2005年以上，你可以使用 ROW_NUMBER 像这样...

<预类=郎-SQL prettyprint-覆盖> SELECT唯一键，COL2，COL3
$ FROM b $ b（
选择唯一键，COL2，COL3，ROW_NUMBER）OVER（ORDER（BY唯一键）AS ROWNUM
FROM YourTable
）分段
，其中sub.RowNum BETWEEN @startRow AND @endRow


如果你真的没有唯一键或添加一个，那么你可以使用的可能性...

<预类=郎-SQL prettyprint-覆盖> ROW_NUMBER（）OVER（ORDER BY（选择0））


...尝试和维持自然秩序的记录存储。但是，这取决于你的数据结构，这是不是保证你如何想它准确地工作。因此，测试移植将是更重要的，但我敢肯定你会做那无妨; - ）

Scenario: we are fetching rows from SQL Server to C#.Net console application and doing action on the retrieved data from SQL Server through stored procedure; after the action is performed the new data is stored into the MongoDB using C#-MongoDB-Driver.

Issue: There are billions of rows. My stored procedure contains query as follows:

select * from table_name

To work out some batch-wise logic there is no identity column nor any date columns or such.

Information: As of now the application is fetching the records upto 3500 - 5000 records and storing into MongoDB and then it throws an error which is as follows:

System.Runtime.InteropServices.SEHException (0x80004005): External component has thrown an exception.

Question: Can anyone suggest me some logic to work out for batch-wise read/fetch from SQL Server?

解决方案

If you can't use OFFSET-FETCH in SQL Server 2012 and assuming the table has a primary key or column(s) that allow you to uniquely identify a row, lets call it UniqueKey, then in 2005 upwards you could use ROW_NUMBER like this...

SELECT UniqueKey, col2, col3 
FROM 
(
  SELECT UniqueKey, col2, col3, ROW_NUMBER() OVER (ORDER BY UniqueKey) AS RowNum 
  FROM YourTable
) sub
WHERE sub.RowNum BETWEEN @startRow AND @endRow

If you really have no unique key or possibility of adding one then you could use...

ROW_NUMBER() OVER (ORDER BY (SELECT 0))

...to try and preserve the natural order that the records are stored in. But, depending on your data structure, this is not guaranteed to work exactly how you want it to. So, testing the migration would be even more important, but I'm sure you will be doing that anyway ;-)

这篇关于什么是获取记录分批从SQL Server的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！