Resource optimization questions .net

Question

There is one paradigm for resource usages optimization:- Acquire late and release early. considering this paradigm. I always see the following sequence to db call :

open conneciton
create command
do validation
set parameter
finally execute command

Is anythng wrong if I do?

create command
do validation
set parameter
open conneciton
finally execute command

e.g

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "cus";
cmd.CommandType = CommandType.StoredProcedure;
//Configure input parameters
SqlParameter param = new SqlParameter();
param = cmd.Parameters.Add(new SqlParameter("@id", 2));
param.Direction = ParameterDirection.Input;
SqlConnection conn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=SpringApp;");
conn.Open();
cmd.Connection = conn;
cmd.Prepare();
SqlDataReader reader = cmd.ExecuteReader();

Be sure to close your connection though – gprasant Feb 15 '12 at 17:15 — gprasant, Feb 15 '12 at 17:15

score 2 · Answer 1 · answered Feb 15 '12 at 17:09

2

The second case is a rigth way to go:

Open connection as late as possible and close it as soon as possible.

answered Feb 15 '12 at 17:09

Tigran

61,654
8
86
123

score 2 · Answer 2 · answered Feb 15 '12 at 17:17

Yep the second sequence is fine, in fact if it takes any significant amount of time to create your command is in fact preferable as it leaves the connection open for less time (however normally creating the command takes very little time and so this will have no appreciable difference, so in reality you shouldn't fuss too much over this).

I would recommend that you use the using statement wherever possible in order to dispose of resources and to make it clearer what the lifetime of your objects are:

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "cus";
    cmd.CommandType = CommandType.StoredProcedure;
    //Configure input parameters
    SqlParameter param = new SqlParameter();
    param = cmd.Parameters.Add(new SqlParameter("@id", 2));
    param.Direction = ParameterDirection.Input;

    using (SqlConnection conn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=SpringApp;"))
    {
        conn.Open();
        cmd.Connection = conn;
        cmd.Prepare();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {

        }
    }
}

H H · Accepted Answer · 2012-02-15T18:23:46.930

1

Yes, the second sequence is better. How much depends on how much work goes into 1,2,3. I usually would not consider CreateCommand and SetParameter as big issues but Validation might require some time and it might even lead to canceling the whole thing. A pity if you have already opened the Connection and then find out the data is invalid.

edited Feb 15 '12 at 18:23

answered Feb 15 '12 at 17:11

H H

263,252
30
330
514

score 1 · Answer 4 · edited May 23 '17 at 10:34

The second version is a tiny tiny bit more efficient. But actually it does not matter at all. I doubt you can measure it. The creation of the command is not an RPC activity. A command is just a few bytes in your RAM. It is cheap.

But: If you care about resource management it is a death sin not to put your disposables into a using block! If an exception occurs you will leak a SqlConnection!

Your call to prepare is a no-op (What does SqlCommand.Prepare() do and when should it be used?) and can be removed.

score 0 · Answer 5 · answered Feb 15 '12 at 17:13

you are doing the exact opposite you say on your paradigm.

1) open conneciton is the oposite of Acquire late

you should do everything that's possible, specially validation, before opening your connection. When its done, use an "using" statement to open and auto close the connection

Resource optimization questions .net

5 Answers5