ExecuteReader on large dataset is slow to return

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
betawarz
Posts: 10
Joined: Mon 06 Dec 2010 18:15

ExecuteReader on large dataset is slow to return

Post by betawarz » Tue 27 Sep 2011 16:27

Hi all,

I'm executing a select query that returns a large amount of data. I'm currently using ExecuteReader to read the results. This function call can take up to 30 seconds to return, though. I see that theres the ExecutePageReader, but that sounds like what I'd use for a subset of the results.

Is there a way to maybe load in the results in a lazy manner, and not have to wait for the entire result set to be loaded? Or, is that what ExecuteReader is doing?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 28 Sep 2011 15:22

cmd.ExecuteReader() executes simple select very quickly. But if your query contains "ORDER BY", "JOIN", etc, this makes reader to process all records of the result set. So, use a simple select if possible.
As an alternative, delegate the operation of preparing your result set to the server side by creating materialized view (e.g.: http://download.oracle.com/docs/cd/B137 ... s_6002.htm ), and query the result set with your cmd.ExecuteReader() from the materialized view.

Post Reply