C# - Native SQL Provider Sample Speed Tests

By Robbe D. Morris

Printer Friendly Version


Robbe & Melisa Morris


Like many software engineers, I endeavor to make the transition from advanced development using Microsoft's COM environment to the fun and exciting .NET Framework (especially C#).  This transition has proved to be more complex than I had originally thought.  Thus, I've opted to try and isolate key portions of C# (like data access) and focus my energies at becoming well versed in these areas and worry about moving onto more advanced (although less often used) aspects of the language.  Of course, that brings us to today's topic, speedy data access via ADO.NET.
As with all new technologies, I have a tendency to question the suggested methodologies for best practice.  Obviously, since the technology hasn't been out long, most people are just trying to make it work properly or display a wide variety of options.  This is understandable but as a consequence gets us in the habit of writing inefficient code right from the start.  Bad habits can be hard to overcome...
 


 
One of the main things I wanted to get out of the way was how to best return a disconnected set of data.  With ADO 2.6, we could simply use the most efficient arguements with our recordset and set the activeconnection property to nothing.  However, ADO.NET is a complete rewrite of the data access technologies and our standard recordset is now missing.  Today, I'll focus on the custom SQL provider designed specifically for SQL Server use in ADO.NET.  The OLEDB providers are also available but have been documented to perform slower with SQL Server.
In the test, there are 3 different sample queries taken from the Northwind database.  There are also 5 different functions designed to provide the capability of returning the data disconnected from the SqlReader or SqlDataAdapter as soon as possible.  However, take notice that for testing purposes I've reused the same connection object for all 5 functions in order to try and ensure the times recorded weren't skewed by variable connection times.  During testing, I reviewed times both from compiled C# executables as well as the ASP.NET code below.  It is important to note that while the times were slightly different, the overall "rankings" didn't change.  Let's take a look at the functions and their average speed time results in milliseconds:

Test #1 select companyname,contactname from customers
Test #2 select categoryid,categoryname from categories
Test #3 SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = 'ANATR'
AND C.CustomerID = O.CustomerID
AND O.OrderID = OD.OrderID
AND OD.ProductID = P.ProductID
ROUP BY ProductName

Method Purpose
ADOGetXMLDoc Load returned data into a manually built XML string and loaded into an XMLDocument object.
ADOGetArray Load returned data into a manually built two dimensional array similar to recordset.getrows() in ADO 2.6.
ADOGetDataSet Load returned data into an ADO.NET disconnected data set.
ADOGetDataTable Load returned data into an ADO.NET disconnected data table without the overhead of the entire data set class.
ADOGetDataSetXML Load returned data into an ADO.NET disconnected data set and then export the data into an XMLDocument object.

MethodMilliseconds
Test #1
ADOGetArray300.432
ADOGetDataSet320.4608
ADOGetDataTable294.5796
ADOGetDataSetXML1151.656
ADOGetXMLDoc921.3248

Test #2
ADOGetArray180.2592
ADOGetDataSet170.2448
ADOGetDataTable165.7948
ADOGetDataSetXML230.3312
ADOGetXMLDoc190.2736

Test #3
ADOGetArray130.1872
ADOGetDataSet140.2016
ADOGetDataTable110.9854
ADOGetDataSetXML330.4752
ADOGetXMLDocNot Supported

ADOGetArray normally outperformed the other functions besides ADOGetDataTable by a wide margin.  However, there were times during the testing phase that the ADOGetDataSet function outperformed the ADOGetArray.  For now, I'm inclined to use the GetDataTable methodology particularly with larger sets of returned data to avoid the overhead of the DataSet object when much of it's powerful features really aren't needed for basic queries and it is much simpler to work with than the GetArray methodology.
With the advent of SOAP and remote XML procedure calls, the need to efficiently turn returned data into XML is a must.  The test results here are interesting to say the least.  Depending on the amount of data retrieved, the data set may or may not be the fastest way to create a valid XML string for loading into an XML Document object.  In Query #1 there is much more data to be loaded.  However, in Query #2 there is less data and the data set method works considerably faster.  You may wonder why Query #3 has no results for the manually created XML option (ADOGetXMLDoc).  If you look closely, you'll see the following SQL phrase added to the query FOR XML AUTO.  This phrase is needed by the ExecuteXMLReader method of the SqlCommand object in order to return a proper XMLReader object.  It does not support certain aggregate functions like GROUP BY and thus could not be used in the test.
Out of curiosity, I put together a couple of matching functionality tests with SQLOLEDB/ASP3.0/ADO 2.6 for the sole purpose of comparing what we are used today .v what .NET brings to the table.  It is not a true apples to apples comparison between ADO.NET and ADO 2.6 as the code is being run under different runtimes.  Here are the comparable results:

MethodMilliseconds
Test #1
ADOGetArray652.3438
ADOGetDataSetXML1140.625

Test #2
ADOGetArray312.5
ADOGetDataSetXML398.4375

Test #3
ADOGetArray261.7188
ADOGetDataSetXML429.6875

Frankly, I was surprised that the old ADO 2.6 recordset method GetRows() was left out of ADO.NET.  So many engineers have grown accustomed to the recordset.GetRows function and its speed that it just makes sense to include an option for a faster retrieval of data into a disconnected array.  Probably would have made it a least a little simpler to convert ASP code to ASP.NET code as well.  In any event, the .NET results show a clear improvement in speed over ADO 2.6 and helps (at least in some small part) make the case for the upcoming transition to .NET painful as it may be.
While this test may not reflect a truly scientific approach, it surely underlines the need to dig down deep and actually test your code when attempting to improve performance.  When the production release of .NET comes out, I'll be rerunning this test.  I actually ran the test against Beta 1 to look for differences.  While Beta 1 was a bit slower, similar results were seen.  If you have any questions or would like to make a suggestion, please feel free to email me.

C# Source Code Used For This Test (ASP.NET)
  <%@ Import Namespace="System.Data" %>
  <%@ Import Namespace="System" %>
  <%@ Import Namespace="System.Xml" %>
  <%@ Import Namespace="System.Data" %>
  <%@ Import Namespace="System.Data.SqlClient" %>

  <script Language="C#" runat="server">

  protected void Page_Load(object sender, EventArgs e)
  {
 
       Response.Write("<HTML><BODY>");
       Response.Write("<TABLE BORDER=2 WIDTH='40%' ALIGN=LEFT>"); 
       Response.Write("<TR><TD ALIGN=LEFT><b>Function Name</b><TD ALIGN=LEFT><b>Milliseconds</b></TD></TR>");
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>");
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2><b>ADO Test Option #1</b></TD></TR>");
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>");
       RunTest(1);
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2><BR></TD></TR>");
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2><b>ADO Test Option #2</b></TD></TR>");
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>");
       RunTest(2);
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2><BR></TD></TR>");
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2><b>ADO Test Option #3</b></TD></TR>");
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>");
       RunTest(3);
       Response.Write("</TABLE></BODY></HTML>");
        
    
  }


   public void RunTest(int nOption)
   {

      ArrayList sRS = new ArrayList();
      int nRetVal=0;
      int nMax = 100;
      string sSQL = GetTestSQL(nOption);  
      SqlConnection oConn = new SqlConnection();
      XmlDocument oXML = new XmlDocument();
      XmlDocument oXML2 = new XmlDocument();
      DataSet oDS = new DataSet();
      DataTable oDT = new DataTable();
      System.DateTime sStartTime;
      TimeSpan elapsed;

      oConn.ConnectionString = "user id=sa;password=;initial catalog=northwind;data source=(local)";
      oConn.Open();
		  
     
 
      // Start ADOGetArray Test
     sStartTime = System.DateTime.Now;
     for (int i=0;i<nMax;i++)
	{ nRetVal = ADOGetArray(oConn,sSQL,sRS); }
    elapsed = System.DateTime.Now - sStartTime;
    WM("ADOGetArray",elapsed.TotalMilliseconds.ToString()  );
  
    //  Syntax for iterating through ArrayList that
   //   contains arrays for each element.  This
  //    writes the first column for the first row.
   //	foreach (string[] oArray in sRS) 
   //	{ Console.WriteLine(oArray[0]); }
	 

    // Start ADOGetDataSet Test
    sStartTime = System.DateTime.Now;
    for (int i=0;i<nMax;i++)
	{ nRetVal = ADOGetDataSet(oConn,sSQL,oDS); }
    elapsed = System.DateTime.Now - sStartTime;
    WM("ADOGetDataSet",elapsed.TotalMilliseconds.ToString()  );

 // Start ADOGetDataTable Test
    sStartTime = System.DateTime.Now;
    for (int i=0;i<nMax;i++)
	{ nRetVal = ADOGetDataTable(oConn,sSQL,oDT); }
    elapsed = System.DateTime.Now - sStartTime;
    WM("ADOGetDataTable",elapsed.TotalMilliseconds.ToString()  );

    // Start ADOGetDataSetXML Test
   sStartTime = System.DateTime.Now;
   for (int i=0;i<nMax;i++)
     { nRetVal = ADOGetDataSetXML(oConn,sSQL,oXML2); }
   elapsed = System.DateTime.Now - sStartTime;
   WM("ADOGetDataSetXML",elapsed.TotalMilliseconds.ToString()  );
 
   if (nOption != 3)
   {
    // Start ADOGetXMLDoc Test
      sStartTime = System.DateTime.Now;
      for (int i=0;i<nMax;i++)
         { nRetVal = ADOGetXMLDoc(oConn,sSQL,oXML); }
      elapsed = System.DateTime.Now - sStartTime;
      WM("ADOGetXMLDoc",elapsed.TotalMilliseconds.ToString()  );
    }
    else {   WM("ADOGetXMLDoc","Not Supported"); }


   oConn.Close();

   }

  public void WM(string sDesc,string sTime)
  {
  Response.Write("<TR><TD ALIGN=LEFT>" + sDesc + "</TD><TD ALIGN=LEFT>" + sTime + "</TD></TR>");
  Response.Flush();
  }

public string GetTestSQL(int nOption)
  {
	string sSQL=null;
	switch (nOption)
	{
	case 1: 
					
		sSQL = "select companyname,contactname from customers "; 
		break;
			
	case 2:  

		sSQL = "select categoryid,categoryname from categories "; 
		break;

	case 3:
		
		sSQL += "SELECT ProductName, Total=SUM(Quantity) ";
		sSQL += "   FROM Products P, [Order Details] OD, Orders O, Customers C " ;
		sSQL += "   WHERE C.CustomerID = 'ANATR' " ;
		sSQL += "       AND C.CustomerID = O.CustomerID " ;
                sSQL += "       AND O.OrderID = OD.OrderID" ;
		sSQL += "       AND OD.ProductID = P.ProductID" ;
		sSQL += "       GROUP BY ProductName" ; 
        	break;
	}
   return sSQL;
 }


   


		public int ADOGetArray(SqlConnection oConn,string sSQL, ArrayList sRS) 
		{
		    int nRetVal = 0;
		    string [] sTmpRec=null;
			try
			{   
				SqlCommand oCmd = new SqlCommand(sSQL,oConn);
				SqlDataReader oReader = oCmd.ExecuteReader();
				int nCols = oReader.FieldCount;         
				   
				while (oReader.Read())
					{ 
					 sTmpRec = new string[nCols];
					 for (int nCol=0;nCol<nCols;nCol++) 
					    { sTmpRec[nCol] = oReader.GetValue(nCol).ToString();  }
				     	sRS.Add(sTmpRec); 
					}
				 
		          oReader.Close();
 
				nRetVal = 1;
			}
			catch (Exception e) { nRetVal = 2; Response.Write(e.Message);}  
			return nRetVal;
		}
 

		public int ADOGetDataSet(SqlConnection oConn,string sSQL, DataSet oRetDS) 
		{
			int nRetVal = 0;
			try
			{   
				SqlDataAdapter oDA = new SqlDataAdapter(sSQL,oConn);
				oDA.Fill(oRetDS,"Categories"); 
				nRetVal = 1;
			}
			catch (Exception e) { nRetVal = 2;  }  
			return nRetVal;
		}

                                   public int ADOGetDataTable(SqlConnection oConn,string sSQL, DataTable oRetDT) 
		{
			int nRetVal = 0;
			try
			{   
				SqlDataAdapter oDA = new SqlDataAdapter(sSQL,oConn);
				oDA.Fill(oRetDT); 
				nRetVal = 1;
			}
			catch (Exception e) { nRetVal = 2;  }  
			return nRetVal;
		}

		public int ADOGetDataSetXML(SqlConnection oConn,string sSQL, XmlDocument oRetXML) 
		{
			int nRetVal = 0;
			try
			{   
				SqlDataAdapter oDA = new SqlDataAdapter(sSQL,oConn);
				DataSet oDS = new DataSet();
				oDA.Fill(oDS,"Categories"); 
				oRetXML.LoadXml(oDS.GetXml());
				nRetVal = 1;
			}
			catch (Exception e) { nRetVal = 2;  }  
			return nRetVal;
		}

               public int ADOGetXMLDoc(SqlConnection oConn,string sSQL, XmlDocument oRetXML) 
	{
	  string sTmpVal = null;
	   int nRetVal = 0;
	   try
	   {   
	    SqlCommand oCmd = new SqlCommand(sSQL + " FOR XML AUTO",oConn);
	    XmlReader oReader = oCmd.ExecuteXmlReader();
	    while (oReader.Read()) { sTmpVal += oReader.ReadOuterXml(); }  
	        oReader.Close();
                oRetXML.LoadXml("<?xml version='1.0' encoding='utf-8'?><XMLRECORD>" + sTmpVal + "</XMLRECORD>"); 
                nRetVal = 1;
	    }
            catch (Exception e) { nRetVal = 2;  }   
	    return nRetVal;
	}
  </script>

ASP Source Code Used For This Test
 
   
<!--METADATA NAME="Microsoft ActiveX Data Objects 2.6 Library" TYPE="TypeLib" UUID="{00000206-0000-0010-8000-00AA006D2EA4}"-->
 
<%

     Response.Buffer = true
     Response.Expires = -1000
     Response.ExpiresAbsolute = Now() - 1
     Response.AddHeader "pragma","no-cache"
     Response.AddHeader "cache-control","private"
     Response.CacheControl = "no-cache"

     dim mnTot
     dim msSec
     dim moADOCon
     dim moADORec
 
      mnTot = 100

  Class clsTimer
       Private m_StartTimer
       Private Sub Class_Initialize()
             m_StartTimer = Timer
       End Sub
       Public Function GetElapsedTime()
             GetElapsedTime = (timer-m_StartTimer) * 1000
       end Function
   End Class
   

 Sub ADOGetArray(sSQL,sRS)
 
       Dim nCnt
       Dim objTimer
 
        Set objTimer = New clsTimer
       
        For nCnt = 1 to mnTot
            If moADORec.State = adStateOpen Then moADORec.Close
               moADORec.CursorType = adOpenStatic 
               moADORec.LockType = adLockReadOnly
	       moADORec.Open sSQL
               sRS = moADORec.GetRows
        Next        

         msSec = objTimer.GetElapsedTime
         Set objTimer = Nothing
         Call WM ("ADOGetArray", msSec)
         
  End Sub
 
 Sub ADOGetDataSetXML(sSQL,oStream,oXML)

       Dim nCnt
       Dim objTimer
 
        Set objTimer = New clsTimer
       
        For nCnt = 1 to mnTot

            If moADORec.State = adStateOpen Then moADORec.Close
               moADORec.CursorType = adOpenStatic 
               moADORec.LockType = adLockReadOnly
               moADORec.Open sSQL
               moADORec.Save oStream, adPersistXML  
               oXML.LoadXML oStream.ReadText(adReadAll)

        Next        

         msSec = objTimer.GetElapsedTime
         Set objTimer = Nothing
         Call WM ("ADOGetDataSetXML", msSec)

 End Sub 
    

 Sub WM(sDesc,sTime)
    Response.Write("<TR><TD ALIGN=LEFT class=clsFieldLabel>" & sDesc & "</TD>")
    Response.write ("<TD ALIGN=LEFT class=clsBodyText>" & sTime & "</TD></TR>" & vbcrlf)
    Response.Flush()
 End Sub
   
  Function GetTestSQL(nOption)

      Dim sSQL

	  Select Case nOption
	 
	             Case 1:  sSQL = "select companyname,contactname from customers " 
			 
	             Case 2:  sSQL = "select categoryid,categoryname from categories "
 
	             Case 3

                           sSQL = sSQL & "SELECT ProductName, Total=SUM(Quantity) "
	                   sSQL = sSQL & "   FROM Products P, [Order Details] OD, Orders O, Customers C " 
	                   sSQL = sSQL & "   WHERE C.CustomerID = 'ANATR' " 
	                   sSQL = sSQL & "       AND C.CustomerID = O.CustomerID " 
                           sSQL = sSQL & "       AND O.OrderID = OD.OrderID" 
	                   sSQL = sSQL & "       AND OD.ProductID = P.ProductID" 
	                   sSQL = sSQL & "       GROUP BY ProductName" 

       End Select

       GetTestSQL = sSQL  

   End Function

 Sub RunTest(nOption)
   
      Dim sRS
      
      nRetVal=0
      nMax = 100
      sSQL = GetTestSQL(nOption)
       
    Set oXMLDoc = Server.CreateObject("MSXML2.DOMDocument.3.0")
    Set oStream =Server.CreateObject("ADODB.Stream")
       
          oXMLDoc.async = False
                    
     Set moADOCon = Server.CreateObject("ADODB.Connection")
     Set moADORec = Server.CreateObject("ADODB.Recordset")
   
	 moADOCon.CursorLocation = adUseClient
         moADOCon.open "Provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=;Initial Catalog=NORTHWIND"	
     Set moADORec.ActiveConnection = moADOCon
     
     Call ADOGetArray(sSQL,sRS) 
     Call ADOGetDataSetXML(sSQL,oStream,oXMLDoc)
    
      On Error Resume Next
         If moADORec.STATE = adStateOpen Then moADORec.Close
         If moADOCon.STATE = adStateOpen Then moADOCon.Close
         Set moADORec = Nothing
         Set moADOCon = Nothing
         Set oStream = nothing
         Set oXMLDoc = nothing
   End Sub

       Response.Write("<TABLE BORDER=2 WIDTH='90%' ALIGN=LEFT>" & vbcrlf )
       Response.Write("<TR><TD ALIGN=LEFT class=clsFieldLabel>Function Name");
       Response.Write("<TD ALIGN=LEFT class=clsFieldLabel>Milliseconds</TD></TR>" & vbcrlf)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>" & vbcrlf)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2 class=clsFieldLabel>ADO Test Option #1</TD></TR>" & vbcrlf)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>" & vbcrlf)
       Call RunTest(1)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2><BR></TD></TR>" & vbcrlf)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2 class=clsFieldLabel>ADO Test Option #2</TD></TR>" & vbcrlf)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>" & vbcrlf)
       Call RunTest(2)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2><BR></TD></TR>" & vbcrlf)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2 class=clsFieldLabel>ADO Test Option #3</TD></TR>" & vbcrlf)
       Response.Write("<TR><TD ALIGN=LEFT COLSPAN=2></TD></TR>" & vbcrlf)
       Call RunTest(3)
       Response.Write("</TABLE>")

%>



Robbe is a 2004-2008 Microsoft MVP for C# and the .NET Evangelist for Alinean Inc..  He is also the co-founder of EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.