How is memory managed for Database connections and DataReaders

[Mike]

Assume i have a db connection and a datareader for that connection. Now I
perform a query that retrieves 100MB of data. I know that the data reader
gets 1 row at a time. However, there is a result set out there that is
100MB is size.

If I close the dataReader but keep the DB connection open, will the 100MB be
freed up after closing the DataReader?

Can I continue reusing the same DB connection and creating and destroying
datareaders assigned to that connect and trust that the memory after the
datareader close will be freed?

Thanks

 

[Quoc Linh]

Assume i have a db connection and a datareader for that connection. Now I
perform a query that retrieves 100MB of data. I know that the data reader
gets 1 row at a time. However, there is a result set out there that is
100MB is size.

If I close the dataReader but keep the DB connection open, will the 100MB be
freed up after closing the DataReader?

Can I continue reusing the same DB connection and creating and destroying
datareaders assigned to that connect and trust that the memory after the
datareader close will be freed?

Thanks

The rule for me is usually is: If Displose() method exists on the
objects, use it by all means. Otherwise, trust that memory will be
managed by .Net.

Yes, you can continue to use the connection, but best practice is
don't reuse it and let the connection pool does it magic work.
Typically, do this in your code:

private void f()
{
using (SqlConnection conn = new SqlConnection())
{
// Use conn in here
}
}

* note: code typed up from memory and didn't verify for syntax

Quoc Linh

 

[Samuel R. Neff]

If you use a datareader then it theoretically only reads one row at a
time. So the entire 100mb will not be in memory in your .NET
application in the first place.

The data reader may do some prefetching depending on the
implementation, the socket connection may do some buffering, and the
database most likely will do caching, so more than one record will be
in memory, but it's generally safe to think of a datareader as a
one-record-in-memory data access method.

When you close the data reader the one record it cached will be
released and available for collection. The cursor in the db will also
be released so the db can release whatever memory it was using.

Yes you can continue using that connection and not worry about memory
management in .NET. Just make sure you use the using() {} pattern
every time so you are sure to close the readers even on error. Don't
hold open the connection longer than needed though--connection pooling
makes resuing connections less important than would otherwise be the
case.

Note that a DataSet / DataTable would hold the entire result set in
memory and keep it in memory until all references are removed (DataSet
does have Dispose(), but it's only there due to it's object hierarchy
and it's Dispose method actually doesn't do anything).

HTH,

Sam

 

[Mike]

I am currently using a .NET OLEDB provider which does not support connection
pooling. If i were using an ADO.NET data provider which supports connection
pooling would this be a better solution than managing connections myself? I
need to speed things up as much as possible.

Thanks

 

[Samuel R. Neff]

Before switching to improve performance always check to see if the
thing you think is causing an issue really is.

I'm working with a ADO.NETfor a different database (SQLite) that also
does not support connection pooling and there was some discussion as
to whether connection pooling would be beneficial for this database.
It turned out that the answer depended a lot on the schema. For a
small size database the cost of opening a connection was under a
millisecond which made pooling useless. However, for my more medium
sized schema the cost is 17ms which made pooling essential.

So I can't give you any specific advice, especially without knowing
what db you're using and what other provider options you have, other
than to say don't change anything without being sure it's a change for
the better through testing and don't assume anything is a performance
problem (or isn't) until it's tested.

Good luck,

Sam