净 - 创建一个随需应变的记录，而不是拉一切到内存中一次 [英] .Net - Creating an On-Demand recordset rather than pulling everything into memory at once

问题描述

我不知道是否这个称号是有道理的，但我的情况是这样的：

I don't know if this title makes sense, but my situation is as follows:

我在写一个.net程序来查询一个Oracle数据库对历史的大量（约2年价值每天的数据），并需要创建一个分隔符的文本文件，从这个返回的查询通过发送到客户端来填充其新的数据库（最终文件大小约为4GB）。

I'm writing a .Net program to query an Oracle DB for an enormous amount of history (about 2-years worth of daily data) and need to create a delimited text file from this returned query to send through to a client to populate their new database (final file size will be about 4gb).

我目前的code基本上看起来如下：

My current code basically looks as follows:

Dim strSQLQuery as string = (My query to get all the data)
Dim cmd = New OracleCommand(strSQLQuery, conn)
...
Using Reader As OracleDataReader = cmd.ExecuteReader()
    ... write to text file ...
End Using

这code的伟大工程，但我的问题是，该查询返回这样一个庞大的记录，所以我很害怕运行内存上的 cmd.ExecuteReader（）语句。

This code works great, but my problem is that the query returns such a huge recordset and so I'm afraid of running out of memory on the cmd.ExecuteReader() statement.

我的问题是，如果有任何种类的净方式，而不是处理整个查询并返回整个记录一次，所以要记录的回报更可观的块或类似的东西？

My question is if there is any kind of way in .Net to, rather than process the whole query and return the whole recordset at once, so make the recordset return in more sizable chunks or something like that?

当然再一个解决办法是打破了查询本身（这是我能做的），但我也想知道是否有goign下来这条路之前在.net更好的解决方案已经存在的...

Of course then one solution would be to break up the query itself (which I could do), but I'm also curious to know if there is a better solution already existent in .Net before goign down that route...

此外，虽然这code是用VB写的，我同样享受着在任何VB或C＃的解决方案。

Also, although this code is written in VB, I'm equally as comfortable with solutions in either VB or C#.

谢谢！

推荐答案

在OracleDataReader不会把内存中的所有记录。看FETCHSIZE财产该控制的实际缓存，直到我们需要第二个往返到数据库中记录的金额。

The OracleDataReader won't put all records in memory. Look at the Fetchsize property this controls the amount of records actually cached until we need a second roundtrip to the database.

如果您希望甲骨文更快地返回行，例如，你可以试试

If you want Oracle to return rows faster eg., you can try the

/*+ FIRST_ROWS(n) */

提示在查询相较于

hint in your query vs the

/*+ ALL_ROWS */ 

提示如果你能等待所有的数据。

hint if you can wait for all the data.

这篇关于净 - 创建一个随需应变的记录，而不是拉一切到内存中一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！