1

I have the return ExecuteScalar inside using blocks. And when I run the method for the first time it takes some time(I am using localhost). When I run same method for the second time it is very quick, like the connection was open. WHy is it so quick the second time? 

(...)
using (var sqlConnection = new SqlConnection(connString))
        {
            using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
            {

                sqlCmd.Parameters.Add("@database", System.Data.SqlDbType.NVarChar).Value = "dbName";

                sqlConnection.Open();
                return Convert.ToInt32(sqlCmd.ExecuteScalar()) == 1;
            }
            ;
        }

or here:

using (var sqlConnection = new SqlConnection(connString))
        {
            using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
            {
                sqlCmd.Parameters.Add("@Param1", System.Data.SqlDbType.NVarChar).Value = "ParamValue";
                sqlConnection.Open();
                sqlCmd.ExecuteScalar();
                if ((int)sqlCmd.ExecuteScalar() != 1)
                {
                    using (SqlCommand command = new SqlCommand("CREATE TABLE TableName (ID int IDENTITY(1,1) PRIMARY KEY, (structure code here...)", sqlConnection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

In any case, the second time I run the method that has these I get almost instant response.

positive perspective
  • 339
  • 1
  • 3
  • 18
  • 2
    Can you post your code? Otherwise we are blindly guessing what is going on.. – Broots Waymb Jan 22 '19 at 19:32
  • 4
    Connection pooling? – Kenneth K. Jan 22 '19 at 19:33
  • 1
    sure, I will post some code straight away – positive perspective Jan 22 '19 at 19:34
  • 1
    Your data retrieval is quicker the second time likely due to the fact that your server has cached the result set. This is something that can be tested by running your code, retrieving the data (time it), and then clearing the cache of your server, and trying again. – haag1 Jan 22 '19 at 19:34
  • 1
    so it is not the matter of connection not being disposed after the execution as I thought might be? – positive perspective Jan 22 '19 at 19:34
  • @positiveperspective as far as I'm aware, dropping a connection will not mean that the cache is necessarily cleared as well, but I could be wrong there. – haag1 Jan 22 '19 at 19:35
  • I will post some code to clarify – positive perspective Jan 22 '19 at 19:36
  • It could be method jitting, connection pooling, reading in configuration settings from an app.config/web.config or something else. You would need to create an [mcve] if you want a more specific answer. – Igor Jan 22 '19 at 19:42
  • the second one maybe faster because it looks like the logic was like "if TableName does not exist, create table TableName". this idles on second execution. actually creating a new table is ddl and takes time. – Cee McSharpface Jan 22 '19 at 19:42
  • So, I could actually explicitly close connection after each transaction to isolate problem => if it is still quick then it is not the connection being open problem. Right> – positive perspective Jan 22 '19 at 19:46
  • Connection pooling happens at the application level so even when you call `Close`/`Dispose` on a `SqlConnection` instance the same underlying connection could be reused by the next created instance of `SqlConnection`. This is desirable as creating connections are expensive. So closing your connections will not necessarily isolate any problem. – Igor Jan 22 '19 at 19:48
  • If you want to figure out why it is slow you need to profile the app and figure out what call is slow and go from there. – Igor Jan 22 '19 at 19:49
  • I am not concerned about slowliness. Rather about the fact it is so quick every second time the method is run. I want to make sure the connection is closed after the sqlcommand execution. I've read the using blocks should dispose the connection. BTW, there is a bunch of methods run one after another in a row and all of them have using blocks around the execution – positive perspective Jan 22 '19 at 19:50
  • Correct, you want to wrap your `SqlConnection` instances (and as a general rule all instances where the type implements the `IDisposable` interface) in `using` blocks to ensure that external resources are cleaned up. – Igor Jan 22 '19 at 19:52
  • At this point I am sure I have using blocks wrapping each SqlConnection instance. – positive perspective Jan 22 '19 at 19:54

0 Answers0