OutOfMemoryException when retrieving rows from a large table

OutOfMemoryException when retrieving rows from a large table

Postby kiki » Wed 23 Mar 2011 21:50

Hello,

We are retrieving data from a large table by using DevArt.Oracle connector.

We got the OutOfMemoryException when retrieving more than 1000 rows from the table. We are wondering if there's a buffer limit to check to avoid such issue. Do you have any suggestion?

System.OutOfMemoryException
Exception of type 'System.OutOfMemoryException' was thrown.
Stack Trace:
at Devart.Data.Oracle.OracleDataReader.a()
at Devart.Data.Oracle.OracleDataReader.Read()


Here's the table DDL:

CREATE TABLE "SCALABILTY"."COLUMNS_113"
(
"WBIMP_ID" NUMBER(*,0) NOT NULL ENABLE,
"WBITRAN_ID" NUMBER(*,0) NOT NULL ENABLE,
"WBIMP_STATUS" VARCHAR2(40),
"WBIMP_MSG" VARCHAR2(4000),
"WBIMP_PROC_DATE" DATE,
"WBITRAN_FILENAME" VARCHAR2(256),
"WBITYP_NAME" VARCHAR2(40) NOT NULL ENABLE,
"WBIMP_RECNUM" NUMBER(*,0) NOT NULL ENABLE,
"A" VARCHAR2(1000),
"B" VARCHAR2(1000),
"C" VARCHAR2(1000),
"D" VARCHAR2(1000),
"E" VARCHAR2(1000),
"F" VARCHAR2(1000),
"G" VARCHAR2(1000),
"H" VARCHAR2(1000),
"I" VARCHAR2(1000),
"J" VARCHAR2(1000),
"K" VARCHAR2(1000),
"L" VARCHAR2(1000),
"M" VARCHAR2(1000),
"N" VARCHAR2(1000),
"O" VARCHAR2(1000),
"P" VARCHAR2(1000),
"Q" VARCHAR2(1000),
"R" VARCHAR2(1000),
"S" VARCHAR2(1000),
"T" VARCHAR2(1000),
"U" VARCHAR2(1000),
"V" VARCHAR2(1000),
"W" VARCHAR2(1000),
"X" VARCHAR2(1000),
"Y" VARCHAR2(1000),
"Z" VARCHAR2(1000),
"AA" VARCHAR2(1000),
"AB" VARCHAR2(1000),
"AC" VARCHAR2(1000),
"AD" VARCHAR2(1000),
"AE" VARCHAR2(1000),
"AF" VARCHAR2(1000),
"AG" VARCHAR2(1000),
"AH" VARCHAR2(1000),
"AI" VARCHAR2(1000),
"AJ" VARCHAR2(1000),
"AK" VARCHAR2(1000),
"AL" VARCHAR2(1000),
"AM" VARCHAR2(1000),
"AN" VARCHAR2(1000),
"AO" VARCHAR2(1000),
"AP" VARCHAR2(1000),
"AQ" VARCHAR2(1000),
"AR" VARCHAR2(1000),
"as" VARCHAR2(1000),
"AT" VARCHAR2(1000),
"AU" VARCHAR2(1000),
"AV" VARCHAR2(1000),
"AW" VARCHAR2(1000),
"AX" VARCHAR2(1000),
"AY" VARCHAR2(1000),
"AZ" VARCHAR2(1000),
"BA" VARCHAR2(1000),
"BB" VARCHAR2(1000),
"BC" VARCHAR2(1000),
"BD" VARCHAR2(1000),
"BE" VARCHAR2(1000),
"BF" VARCHAR2(1000),
"BG" VARCHAR2(1000),
"BH" VARCHAR2(1000),
"BI" VARCHAR2(1000),
"BJ" VARCHAR2(1000),
"BK" VARCHAR2(1000),
"BL" VARCHAR2(1000),
"BM" VARCHAR2(1000),
"BN" VARCHAR2(1000),
"BO" VARCHAR2(1000),
"BP" VARCHAR2(1000),
"BQ" VARCHAR2(1000),
"BR" VARCHAR2(1000),
"BS" VARCHAR2(1000),
"BT" VARCHAR2(1000),
"BU" VARCHAR2(1000),
"BV" VARCHAR2(1000),
"BW" VARCHAR2(1000),
"BX" VARCHAR2(1000),
"by" VARCHAR2(1000),
"BZ" VARCHAR2(1000),
"CA" VARCHAR2(1000),
"CB" VARCHAR2(1000),
"CC" VARCHAR2(1000),
"CD" VARCHAR2(1000),
"CE" VARCHAR2(1000),
"CF" VARCHAR2(1000),
"CG" VARCHAR2(1000),
"CH" VARCHAR2(1000),
"CI" VARCHAR2(1000),
"CJ" VARCHAR2(1000),
"CK" VARCHAR2(1000),
"CL" VARCHAR2(1000),
"CM" VARCHAR2(1000),
"CN" VARCHAR2(1000),
"CO" VARCHAR2(1000),
"CP" VARCHAR2(1000),
"CQ" VARCHAR2(1000),
"CR" VARCHAR2(1000),
"CS" VARCHAR2(1000),
"CT" VARCHAR2(1000),
"CU" VARCHAR2(1000),
"CV" VARCHAR2(1000),
"CW" VARCHAR2(1000),
"CX" VARCHAR2(1000),
"CY" VARCHAR2(1000),
"CZ" VARCHAR2(1000),
"CLIENT_ID" NUMBER(*,0)
DEFAULT -1 NOT NULL ENABLE,
PRIMARY KEY ("WBIMP_ID"))

Thanks!
kiki
 
Posts: 7
Joined: Wed 23 Mar 2011 21:05

Postby Shalex » Fri 25 Mar 2011 15:12

1. Are you using the OCI (via Oracle client) or Direct mode? If OCI, try Direct Mode. Does the problem persist in Direct Mode as well? If not, it can be the OCI problem - try another version of Oracle client.
2. Could you please describe your scenario? Are you using ORM (LinqConnect or Entity Framework)?
a) if you are working with OracleDataReader, try setting the FetchSize property of your OracleCommand explicitly;
b) if you are filling the OracleDataTable object, try the FillPage() method of the OracleDataAdapter object to control the amount of data you want to receive, and to call the Clear() method of the OracleDataTable object if you don't need its current content any more.
3. A small test project with the test data (DDL + DML) will be appreciated. You can send it to us via our contact form. Also please specify the versions of your dotConnect for Oracle (x.xx.xxx), Oracle server, and Oracle client.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Postby kiki » Mon 04 Apr 2011 20:07

We are using OCI mode and OracleDataReader, I tried set FetchSize = 100 and it works.

Thanks very much.
kiki
 
Posts: 7
Joined: Wed 23 Mar 2011 21:05


Return to dotConnect for Oracle