slow query response

mkashyap · Post by **mkashyap** » Fri 12 Mar 2010 21:15

Hi,

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

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.

Thanks.

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