slow query response

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 11
Joined: Mon 09 Nov 2009 21:26

slow query response

Post by mkashyap » Fri 12 Mar 2010 21:15


We recently converted from using Oracle driver (OracleDataaccess.dll) to dotConnect for Oracle in the direct mode. Our Devart version is

I have a query returning from a view, which results in about 60,000 records.
When using Oracle I had optimized the performance by using the fetchsize property on the command object to return a Reader.

I did the same with the Devart OracleCommand object setting the fetch size to 10,000 rows but it seems to have no effect on the query return time whether I set a fetchsize or not.

Am I doing the right thing? Are there other ways to optimize the performance.


Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 15 Mar 2010 13:08

The OracleCommand.FetchSize property gets or sets the number of rows that will be transferred across the network per one round trip with a database. The default value of this property is 0. This means that its value will be determined automatically for better performance of the fetch operation. Actually, you can determine experimentally the best value for your case by setting values manually. Theoretically, the worst performance is when FetchSize=1, the best one is when FetchSize=60000 (for your view). Please take into account the size of your records to exclude the possibility of memory leak (discussed in this thread).

Post Reply