This post originated from an RSS feed registered with .NET Buzz by John Papa. Original Post: ADO.NET Performance Test: Getting a Single Value Feed Title: John Papa Feed URL: /error.htm?aspxerrorpath=/blogs/john.papa/rss.aspx Feed Description: .NET Code Samples, Data Access, Patterns and Other Musings Latest .NET Buzz Posts Latest .NET Buzz Posts by John Papa Latest Posts From John Papa

I ran some tests using ADO.NET to measure the time it takes to retrieve a single value from a database. All of my tests had the following attributes:

• Each test gets the CompanyName for the specified CustomerID (in Northwind)
• The same connection string, using integrated security
• Stored procedure (vs inline SQL)
• I used a SqlCommand with a parameter for the input value (and the output, for the output parameter test)
• I ran the stored procedures ahead of time to remove the possibility of the first time performance hit
• SqlConnection, SqlCommand and the SqlDataAdatper (where applicable) all used the using statement
• I added the result to a list box in each test, just to show that we did something with the value
• I ran the complete tests over 25,000 iterations, including destruction of all ADO.NET objects (and instantiation)

The techniques that I tested were:

• ExecuteScalar 
  • Retrieving the CompanyName from a stored procedure as the return value of the ExecuteScalar method
• Output Parameters
  • Retrieving the CompanyName from an output parameter of a stored procedure
• DataSet
  • Retrieving the CompanyName from the first row amd colun of a DataSet, as the result of a stored procedure

Here were the test results:

So it turns out that ExecuteScalar test ran slightly faster than the output parameter test, and much faster than the DataSet test. In case the image comes ut fuzzy, here are the results again:

Given that ExecuteScalar is easier to implement that an output parameter, I'd choose that over output parameters. But it is interesting that ExecuteScalar and using output parameters are so close. I found it very interesting to see how slow the DataSet technique was. Kind of figured that it would be slower, but that was somewhat surprising (the DataSet is onyl returning a single row and column).

In case you are wondering, I ran the tests again over iterations of

• 1000
• 10,000
• 100,000
• 250,000

The results were very similar in each case. When I ran the tests for less than 1000, sometimes it ran so quick that the results were 0 ms. Not much help there ;-)

Anyway, I thought it was interesting to see the results of how well or porrly these 3 methods performed. You might wonder why I did not try the DataReader ... well, I'll try that one and re-post later.

case "ExecuteScalar" : 
  dteTimer = DateTime.Now;
  for (int i = 1; i <= iLimit; i++)
  {
    using (SqlConnection cn = new SqlConnection(sCn))
    {
      string proc = "prGetCustomerName";
      cn.Open();
      using (SqlCommand cmd = new SqlCommand(proc, cn))
      {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter parmCustID = new 
          SqlParameter("@CustomerID", SqlDbType.NVarChar, 10);
        parmCustID.Direction = ParameterDirection.Input;
        parmCustID.Value = "FRANK";
        cmd.Parameters.Add(parmCustID);
        sCompany = cmd.ExecuteScalar().ToString();
        lstResults.Items.Add(i.ToString() + ") " + sCompany);
      }
      cn.Close();
    }
  }
  dblPeriod = TimeSpan.FromTicks(DateTime.Now.Ticks - 
    dteTimer.Ticks).TotalMilliseconds;
  lstElapsedTime.Items.Add("ExecuteScalar: " + 
    dblPeriod.ToString() + " ms");
  break;