CLOB Datatype performance problems.
Posted: Wed 12 Nov 2014 03:25
Hi, we are investigating a performance problem when retrieving 5000 keys from a database.
We have pinpointed it down to 1 areas. The data is stored as a clob datatype in the table we are retrieving from. It can take 5-6 minutes to retrieve these values. If i change it to a varchar2 type it will return within seconds.
In ODP.NET, the OracleCommand class has a property called InitialLOBFetchSize which is used to control how much of the LOB is fetched with the row, this probably would solve our issue although it doesn’t seem to be there in the DevArt version.
I since found some minor documentation regarding a Lob Block Size connection string setting. Is this similar to the fetchsize ?
I have tested with Lob Block Size 0-8 and found no improved performance. Some of our key lengths are over 4000 , so we require clob types, but not at the expense of the massive overhead.
I found an old forum post from 3 years ago with similar problems which you responded it was being looked into. but i cant find anything in documentation.
We have pinpointed it down to 1 areas. The data is stored as a clob datatype in the table we are retrieving from. It can take 5-6 minutes to retrieve these values. If i change it to a varchar2 type it will return within seconds.
In ODP.NET, the OracleCommand class has a property called InitialLOBFetchSize which is used to control how much of the LOB is fetched with the row, this probably would solve our issue although it doesn’t seem to be there in the DevArt version.
I since found some minor documentation regarding a Lob Block Size connection string setting. Is this similar to the fetchsize ?
I have tested with Lob Block Size 0-8 and found no improved performance. Some of our key lengths are over 4000 , so we require clob types, but not at the expense of the massive overhead.
I found an old forum post from 3 years ago with similar problems which you responded it was being looked into. but i cant find anything in documentation.