CODEWITHSUNDEEP
c#sql-server-2008
UvarajGopu
UvarajGopu

Reputation: 27

Why ExecuteScalar takes time for first call?

Below is my program to measure the time taken by ExecuteScalar for multiple iteration.

static void Main(string[] args)
{
    string sqlConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";
    SqlConnection connection = new SqlConnection(sqlConnectionString);
    for(int i=0; i <4; i++){
        Stopwatch stopWatch = new Stopwatch();

        string sqlCommand = "Insert into TestTable (SNO, Name) values (" + i + ",' " + i + "')";
        SqlCommand command = new SqlCommand(sqlCommand, connection);
        connection.Open();
        stopWatch.Start();
        var result = command.ExecuteScalar();
        stopWatch.Stop();
        connection.Close();
        Console.WriteLine("Time elapsed to insert row " + i + " : " + stopWatch.ElapsedMilliseconds);
    }
    Console.ReadKey();
}

Output: 

Time elapsed to insert row 0 : 3
Time elapsed to insert row 1 : 1
Time elapsed to insert row 2 : 0
Time elapsed to insert row 3 : 0

My question is why it is taking 3 milliseconds for first iteration and for remaining it is lesser than that.

Thanks in advance.

Upvotes: 1

Views: 216

Answers (3)

gmiley
gmiley

Reputation: 6604

This is due to Connection Pooling. Once you establish a connection (regardless of if you close it or not), as long as your connection string remains unchanged, the connections are pooled which leads to quicker consecutive executions.

Upvotes: 2

Rahul
Rahul

Reputation: 77876

First of all you should be using ExecuteNonQuery() method instead. Now talking about the time ; first time it has to establish the connection and then execute the query but for the later iteration that's not the case any more.

Upvotes: 1

tne
tne

Reputation: 7261

Most likely the establishment of the connection which isn't really ended on close but rather returned to a connection pool and reused on the next iteration.

In general, you may also factor in query plan caching and actual data caching from the DBMS, but in this case that wouldn't really apply for INSERT operations (still, the necessary metadata for it might be cold during the first iteration).

Upvotes: 1

Related Questions