1

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

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).

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

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?

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...

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

Thanks!!!

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • 2
    `cmd.ExecuteReader()` shouldn't pull in all data at once. You are getting back an `OracleDataReader` which then streams data to you as you read row by row. Are you running out of memory on `ExecuteReader` or subsequent reads? – Nikola Radosavljević Feb 21 '14 at 14:43
  • @NikolaRadosavljević, Maybe I'm mistaken... I haven't run it for the full date-range yet. But even when I run it for a month, What's happening is that it takes a **VERY** long time to query the data out the DB and so I assumed it was querying and returning all the data... Is that definitely and incorrect assumption?? – John Bustos Feb 21 '14 at 14:47
  • 2
    What makes you think the result comes back all at once? Reader grabs a row with Read(). Actually it packs a groups of rows at at time for efficiency but not the whole query. – paparazzo Feb 21 '14 at 14:48
  • I truly do apologize - I guess I was (unknowingly) already using the correct tool for the job... Thanks for the clarification!!! – John Bustos Feb 21 '14 at 14:51

2 Answers2

1

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.

Dieter DHoker
  • 421
  • 3
  • 9
  • I truly do apologize - I guess I was (unknowingly) already using the correct tool for the job... Thanks for the clarification!!! – John Bustos Feb 21 '14 at 14:51
1

OracleCommand.ExecuteReader method doesn't load all data up front. It only provides you with OracleDataReader which you then use to load data. It's not specified if OracleDataReader loads data up front, and how much of it, but it does not bluntly pull in all the data requested by the query.

You can even specify that the reader should only pull in field by field from single row in cases when you have large blobs of data. This is done using CommandBehavior.SequentialAccess.

Nikola Radosavljević
  • 6,871
  • 32
  • 44
  • Nikola, thank you so very much for clarifying my pure ignorance! Dieter had already answered and I had already accepted his answer by the time you posted something I could accept, but I truly do thank you SO MUCH for your clarification and explanation on this!!! – John Bustos Feb 21 '14 at 14:57
  • 1
    No problem. SO is a library, it's nice to have reference to some info so I submitted anyway. – Nikola Radosavljević Feb 21 '14 at 15:50