myDataReader.Close() takes forever

[shutterstock]

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

[Oleg]

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.

[shutterstock]

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

[Serious]

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