CODEWITHSUNDEEP
c#sqloptimizationforeach
NocFenix
NocFenix

Reputation: 701

Foreach and SqlConnection Using Statement

I'm trying to figure out which is more efficient to run. I have a foreach of tables and I'm wondering if it should be outside of my using or inside of it. Examples below.

the using (SqlConnection) inside of the foreach:

foreach (string tableName in desiredTables)
{
    using (SqlConnection connection = new SqlConnection(cloudConnectionString))
    {
        connection.Open();

        string query = string.Format("SELECT id, active, createdon, modifiedon FROM {0}", tableName);
        using (SqlCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);

            try
            {
                dAdapter.Fill(dSet, tableName);
            }
            catch
            {
                throw;
            }
        }
    }
}

Versus the foreach inside of the using (SqlConnection):

using (SqlConnection connection = new SqlConnection(cloudConnectionString))
{
    connection.Open();

    foreach (string tableName in desiredTables)
    {

        string query = string.Format("SELECT id, active, createdon, modifiedon FROM {0}", tableName);
        using (SqlCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);

            try
            {
                dAdapter.Fill(dSet, tableName);
            }
            catch
            {
                throw;
            }
        }
    }
}

I'm trying to optimize this for a faster pull from the DB and I'm not sure which way is recommended/better. Advice would be great.

Upvotes: 1

Views: 2793

Answers (2)

D Stanley
D Stanley

Reputation: 152626

You'll have to try it and measure it to be certain, but I doubt you'll see a huge difference either way. Connections are pooled by .NET, so creating them in a loop (I'm assuming you just have a handful of tables, not several hundred) should not be a significant bottleneck. You'll likely spend a LOT more time in network I/O and actually populating the DataSet (which does have a lot of overhead).

Bottom line - fine one way that works (so you have something to revert back to) and try it multiple ways and see if one method makes a significant difference to the application overall.

Upvotes: 3

Tuan Le PN
Tuan Le PN

Reputation: 384

Generally, Open and Close a database connection multiple times in a network environment can be expensive (connection data travels thru your network several rounds, the C# CLR has to allocate and free resource several times, the Garbage Collection has more works to do later...), so you should try to reuse your database connection by putting it outside the loop.

Upvotes: -1

Related Questions