如何遍历一个大的SQL结果集与多个相关表 [英] How to iterate through a large SQL result set with multiple related tables

问题描述

我检索非常大量的记录与SQL Server的多个结果集到我的.NET 3.5的项目。

I am retrieving a very large number of records with multiple result sets from SQL Server into my .NET 3.5 project.

我不能随便拉他们都变成对他们的数据集和工作，因为它会占用太多的内存。我并不需要所有的记录，以拉的一次，但在父表相关的子记录只是一个记录，然后。

I can't just pull them all into a DataSet and work on them, since it would take up too much memory. I don't need to pull all the records as once but just one record in the parent table and then the related child records.

我可以使用DataReader做到这一点，但在这里我关心的是通过所有的记录集合将会需要许多小时的过程。这意味着DataReader的将敞开所有这些，我不认为这是可能的时间。

I could accomplish this with using a DataReader but my concern here is the process of iterating through all the records will take many hours. That means the DataReader would be left open for all those hours which I don't think is possible.

你怎么去的所有记录检索到数据集时处理的记录是不可以可能的，因为大量的，但使用DataReader将要求它是开放的几个小时？

How do you go about processing the records when retrieving all the records into a DataSet is not possible due to the large number but using a DataReader would require it to be open for many hours?

推荐答案

要解决这个问题的一种方法是将数据拆分并进行处理的数据块，同时保持对最后的处理记录信息。

One way to tackle this problem is to split the data and process it in chunks, while keeping information about the last processed record.

这样，如果丢失数据库连接，您就不必从头开始，但你可以继续在最后关卡（你可以每天记录后保存检查点如果是有道理的）。

This way, if the database connection is lost you wouldn't need to start over but you could continue at the last "checkpoint" (you could save checkpoint after every record if that makes sense).

如果每个加载记录的处理需要很长的，这将是有意义的使用数据表，下载一小块数据到内存中，处理它，继续（这提高了数据库资源的使用）。但是，如果处理速度快，大部分的时间都花在下载从数据库中的数据，那么它会更有意义，使用DataReader的（以节省内存）。

If processing of each loaded record takes long, it would make sense to use a DataTable to download a small chunk of data into memory, process it and continue (this improves usage of db resources). But if the processing is fast and most of the time is spent downloading the data from database, then it would make more sense to use DataReader (to save memory).

这篇关于如何遍历一个大的SQL结果集与多个相关表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！