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)

Jon
shutterstock
 
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.
Oleg
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.

thanks
shutterstock
 
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.
Serious
 


Return to dotConnect for MySQL