Here’s a problem. If you run your queries in an environment where the DB and the .NET code are geographically close and the ping times are short, you are less likely to notice this issue. But when running them in totally different locations you may see very poor performance up to behavior which is impossible to work with.
To cut a long story short, after narrowing the problem down, the bad performance was unique to columns with LOB types. Googling this issue showed that this is due to Oracle’s ODP.NET provider. It seems like by default, ODP.NET will not fetch LOBs data by default but defer it until explicitly requested by the calling application.
Luckily, this behavior can be controlled by setting the InitialLOBFetchSize of the OracleCommand. By default it is set to 0 which means ‘defer’. You can set it to the number of bytes you would like to retrieve or simply to -1 to fetch it entirely. From the docs.
“By default, InitialLOBFetchSize is set to 0. If the InitialLOBFetchSize property value of the OracleCommand is left as 0, the entire LOB data retrieval is deferred until that data is explicitly requested by the application. If the InitialLOBFetchSize property is set to a nonzero value, the LOB data is immediately fetched up to the number of characters or bytes that the InitialLOBFetchSize property specifies.”
Read more here:
“When InitialLOBFetchSize is set to -1, the entire LOB data is prefetched and stored in the fetch array.”
Personally I think that the default should be exactly the opposite. It is the responsibility of the developer to include or exclude LOB columns in the SELECT clause. If the developer attempted a “SELECT *” he will notice the lag and will have to modify the query.
I also think that it is a shame that these properties must be set in code and cannot be tweaked in the config file.
Here are some additional tips. They do not relate to the obvious tips of fine tuning your queries or database, but to the amount of data is to be passed over the network.
- There is also a InitialLONGFetchSize that you can set to -1 to allow prefetch of LONG and LONG RAW data types.
- If you set the CommandTimeout property to 0, it will be infinite (that goes also for MySQL, SQLServer and DB2). You must take into consideration that setting the LOBFetchSize to -1 will solve just the prefetch problem but the data might still take a lot of time to be fetched. Also note that the different documentations do not recommend setting the timeout to infinite, but perhaps you should still increase it for queries that are supposed to retrieve lots of data.
- You should also consider changing the Connection Timeout. This is usually done via the connection string. Consult http://www.connectionstrings.com how to do this.
- Change your queries to retrieve not the entire table but explicit columns that you actually need.