CLOB Datatype performance problems.

CLOB Datatype performance problems.

Postby roachie » 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.
roachie
 
Posts: 1
Joined: Wed 12 Nov 2014 03:18

Re: CLOB Datatype performance problems.

Postby Pinturiccio » Thu 13 Nov 2014 17:26

We have reproduced the performance issue. We will investigate the possibility of implementing a property similar to the InitialLOBFetchSize property of the ODP.NET provider and post here about the result as soon as possible.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44

Re: CLOB Datatype performance problems.

Postby Pinturiccio » Wed 14 Jan 2015 16:47

We have added the InitialLobFetchSize property to the OracleCommand. This property specifies the amount of data OracleDataReader initially fetches for LOB columns. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44

Re: CLOB Datatype performance problems.

Postby Pinturiccio » Thu 15 Jan 2015 16:15

New build of dotConnect for Oracle 8.4.333 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=31098
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle