DataReader getting slower after fetching 70000 records

  • Thread starter Thread starter Chetan
  • Start date Start date
C

Chetan

Hi,

I am invloved in a re-engineering project, which requires a table with
900000 records to be fetched in memory. I am using DataReader for the
same but surpisingly; it is taking 10 minutes to fetch 80000 records.
On debugging, I noticed that DataReader is getting slow in fetching the
records after 70000.

I have tried using the following providers, but same result

1. ODBC.NET
2. OLEDB.NET
3. ODP.NET


I am also pasting the code using OLEDB Provider for reference.

OleDbConnection objCon = new OleDbConnection(ConnString) ;
objConnection.Open() ;

m_strQuery = @"SELECT DEALID,CMDDATE, SEGMENT1 FROM DELIVERYSHAPE WHERE
DEALID < 20000 " ;

This query will fetch 84,000 records

OleDbCommand objCommand = new OleDbCommand(m_strQuery, objCon) ;

OleDbDataReader objReader = objCommand.ExecuteReader() ;
while ( objReader.Read() )
{
lCounter ++ ;
}

Database is Oracle 9i and client machine is Win XP.

Any suggestions / help / link / pointers would be highly appreciated.

Regards
Chetan Sawhney
 
In which structure do you store those records ? It could perhaps page
depending on the size you reached...

Are you sure you can't do anything else than having all those records in
memory ? For example if you just measure how quick you can get the first
records (let's say only 1000) the best you'll be able to do is likely to
spend 900000/1000 much time to get them all... It should help to set a lower
bar...

Patrice
 
Hi,

I found out the problem. It was the CommandTimeout property of Command
Object. By default, it is 30 seconds. By hit & trial I increased the
time and it worked.

Ideally, it should have given an error or exception but ... it went on
fetching records with a slow speed.

Thanks for your effort Patrice.

- Chetan
 
Back
Top