Persistant db connection vs opening & closing

I've got a sql server 2k8 database that's being populated with several hundred thousand records per day.

I'm currently writing some code that's going to make a call to the db, retrieve n records, pro开发者_Go百科cess them, and write some data back to the db.

There are two ways I see to do this (psuedo code):

function xyz() {
    conn = conn creation code
    conn.open();
    while(not last record) {
        select next 1000 records
        process each record
        last record = true
    }
    conn.close();
    xyz();
}

Essentially creating one connection per batch. The second method:

function xyz() {
    conn = connection creation code
    while(conn.open();) {
        select next 1000 records
        process each record
        last record = true
    }
    conn.close();
    xyz();
}

I'm curious what's better practice. I feel like it's the latter, but it's also going to have a more-or-less permanent/persistent connection to my db. I'm worried about possible memory overruns or some such.

Thoughts?

Scott

ADO.Net Sqlclient provider (which is I assume you will use, since you say is C#) automatically does connection pooling, see SQL Server Connection Pooling (ADO.NET). Pooled connection are not truly closed when you call Close, they are simply returned to the pool. 'Opening' and 'closing' pooled connection is very fast.

unrelated note: you should embed your connection in a using block:

using (SqlConnection conn = new SqlConnection(...))
{
   conn.Open ();
   ...
}

This way you avoid leaking connecitons in exception conditions.

I would handle the connection outside of the function and pass it in. The function is designed to process entries, not to connect to the database and process entries. I would separate those two jobs.

As far as connection opening/closing goes: yes, you should avoid it. It's not too terribly slow on it's own, but if you're just doing processing, there's no reasons to open/close the connection over and over again. If it's due to memory usage (I'm not very familiar with C# by the way, so this part might just be wrong), you should be freeing the result sets. Closing the connection will free the memory associated with it, however, you should be able to get the same effect without having to reconnect.

It depends on what "process each record" means. If that takes a notable amount of time and does not need to maintain the connection, you might want to reconsider keeping the connection open and let the pool handle it.

But if it is constantly reading from/to the connection, you should not artificially disconnect/reconnect.

There is no reason to keep closing and re-opening the connection. It imposes needless load on the database and the network.