myDataReader.Close() takes forever

myDataReader.Close() takes forever

Postby shutterstock » Fri 24 Feb 2006 18:54

we have some large tables - and we do some selects on them.. we often read through the select - and stop somewhere in the middle..

So lets say we have 1M rows - we read 500,000 and then close the DataReader..

this step can sometimes take 10-15 seconds.. simply closing the datareader!

what is going on in this step that is taking so long? is there a better way to do this?

If we dont close it - then it goes much faster.. however, we get the "out of sync" error... so we need to close it..

we use pooling and fetchall=false (otherwise its even slower if this is true)

Posts: 27
Joined: Sat 28 May 2005 20:37

Postby Oleg » Mon 27 Feb 2006 13:49

This is MySQL server behavior restriction: query data is transferred within single network packet. It is impossible to interrupt the transmittal. You can use LIMIT clause of SELECT statement to reduce data size.
Devart Team
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Postby shutterstock » Mon 27 Feb 2006 17:11

Is it not recommended to use a background worker object to close the database reader - while i open a new connection and work with it?

I use cnx pooling - and allow up to 200 connections to sit in the pool.

Posts: 27
Joined: Sat 28 May 2005 20:37

Postby Serious » Mon 27 Feb 2006 17:25

New connection uses another socket so you can fetch data (or close DataReader) in another thread without collisions.

Return to dotConnect for MySQL