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:

      <IMG alt="" src="/photos/jpapa/images/61747/original.aspx" border=0>

      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:

      <TABLE id=Table1 cellSpacing=2 cellPadding=2 align=center border=1> Test Iteration Elapsed ms ExecuteScalar <TD noWrap align=middle>1 <TD noWrap align=right>6577 ExecuteScalar <TD noWrap align=middle>2 <TD noWrap align=right>6468 ExecuteScalar <TD noWrap align=middle>3 <TD noWrap align=right>6577 Output Parameter <TD noWrap align=middle>1 <TD noWrap align=right>6780 Output Parameter <TD noWrap align=middle>2 <TD noWrap align=right>6671 Output Parameter <TD noWrap align=middle>3 <TD noWrap align=right>6749 DataSet <TD noWrap align=middle>1 <TD noWrap align=right>10655 DataSet <TD noWrap align=middle>2 <TD noWrap align=right>10608 DataSet <TD noWrap align=middle>3 <TD noWrap align=right>10608

      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.

        The ExecuteScalar Test
        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;