Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server

An Experimental Performance Comparison on the Methods to Pass Structured Data to SQL Server Stored Procedures from ADO.NET

Rate me:
Please Sign up or sign in to vote.
4.84/5 (19 votes)
23 Dec 2009CPOL10 min read 52.4K   1.5K   48   8
This article makes an experimental comparison on the performance of the different methods to pass structured data to SQL Server Stored Procedure parameters from ADO.NET.

Introduction

This article makes an experimental comparison on the performance of the different methods to pass structured data to SQL Server Stored Procedures from ADO.NET.

Background

It is very common that programmers will want to pass some structured data to Stored Procedures from ADO.NET. For example, if a client application needs to update the scores for thousands of students, it is better to bundle the information in some structured way and pass it to a SQL Server Stored Procedure parameter and let the Stored Procedure process the information in a batch. Compared with calling a Stored Procedure thousands of times, this approach has at least two clear advantages:

  1. Reduce network traffic and therefore improve performance.
  2. By processing the information together in the Stored Procedure, it is much easier to implement the transaction in the Stored Procedure to make sure the updates either succeed or fail for all.

Although it is possible to implement transactions using ADO.NET, when passing the information to SQL Server on a record by record basis, some database objects may be locked by the long going transaction and other users' access to these objects is blocked. By bundling the records and sending them to the Stored Procedure in a structured way, the Stored Procedure can preprocess the data in a form that is best suited for the database update and therefore reduce the time needed to lock the database objects.

The subject of this article has been long discussed by programmers. A CodeProject article: Passing an array or DataTable into a Stored Procedure gave a very good introduction on it. The performance comparison between passing the information in an array and calling the Stored Procedure multiple times clearly favors the batch processing method.

With the introduction of the XML data type in SQL Server 2005 and the introduction of the user defined Table data type in SQL Server 2008, we have more options to pass structured data to SQL Server. In general, we have at least three methods:

  1. In the good old days, we passed structured data to Stored Procedures as Text or VARCAHR by separating pieces of information with special characters.
  2. Passing the information in XML format.
  3. Passing the information as Table Valued Parameters.

This is no doubt that the above three methods all have significant performance advantages over passing information on a record by record basis, but what is the performance comparison among these methods themselves? This article is intended to answer this question by an experiment, and the result shows an impressive performance difference.

To make the comparison practically meaningful, the time measured for the comparison includes the following:

  1. The time to prepare structured data to a form suitable for passing to the Stored Procedure in the client program.
  2. The time spent to upload the data to SQL Server as a Stored Procedure parameter.
  3. The time spent by the SQL Server Stored Procedure to process data to a form that is easy to be used by the Stored Procedure. In this experiment, it is the time to process the data into a temporary table or some equivalent data type.

In this experiment, the Stored Procedures will return the data uploaded to the client program in exactly the same way. This time is also included in the measurement for a more pleasing user experience when conducting the experiment. Since all the Stored Procedures return the same data, the time spent should be similar and should not change the result of the experiment.

This article includes the complete C# code and SQL scripts to setup the test. Interested readers can download the code and set up the environment to repeat the test themselves. If you are not familiar with how to pass structured data to SQL Server Stored Procedures, the code that comes with this article can also serve as a good reference. The client C# code is written in .NET 3.5 in Visual Studio 2008, and the SQL Server version is 2008.

The Data to Pass to SQL Server Stored Procedures

To make the comparison, we need to have some test data to pass to the SQL Server Stored Procedures. I choose the test data based on two criteria:

  1. All the three methods will be using exactly the same set of data.
  2. The data volume needs to be large enough so the performance difference is visible.

A singleton class is created to store the data to pass to the Stored Procedure so it will not be changed during the experiment.

C#
using System;
using System.Data;
    
namespace SQLSPPerformanceComparison
{
    class DataSourceToUpload : DataTable
    {
        private static DataSourceToUpload _thisInstance = null;
 
        private DataSourceToUpload() : base()
        {
            int NoOfRowsToSend = 
                System.Convert.ToInt32(
                  System.Configuration.ConfigurationSettings.AppSettings[
                     "NoOfRowsOfDataInTheTest"]);
 
            Columns.Add("ID", System.Type.GetType("System.Int32"));
            Columns.Add("Name", System.Type.GetType("System.String"));
            Columns.Add("Score", System.Type.GetType("System.Int32"));
 
            Random rd = new Random();
            for (int Idex = 1; Idex <= NoOfRowsToSend; Idex++)
            {
                DataRow row = NewRow();
                Rows.Add(row);
                row["ID"] = Idex;
                row["Name"] = "Student Name No. " + Idex.ToString();
                row["Score"] = 60 + rd.NextDouble() * 40;
            }
        }
 
        public static DataSourceToUpload GetInstance()
        {
            if (_thisInstance == null)
            {
                _thisInstance = new DataSourceToUpload();
            }
 
            return _thisInstance;
        }
    }
}

This class represents a DataTable. The DataTable is filled with dummy test data simulating a list of students with ID, Name, and Score. This class reads from the "App.config" file for the number of rows to fill in the DataTable. In my configuration, the DataTable is 100000 rows. The following picture shows the top 20 rows of the DataTable.

Data to Upload

The ADO.NET Utility

All the SQL Server connections in this experiment go through the following utility class:

C#
using System;
using System.Data;
using System.Data.SqlClient;
 
namespace SQLSPPerformanceComparison
{
    class SQLServerUtility
    {
        public static DataTable GetADataTable(SqlCommand cmd)
        {
            // Create a connection object
            // and obtain the connection string from App.config file
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString =
                System.Configuration.ConfigurationSettings.AppSettings[
                "SQLServerConnectionString"];
 
            cmd.Connection = connection;
            cmd.CommandTimeout = 0;
            connection.Open();
 
            DataTable aTable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
 
            adapter.SelectCommand = cmd;
            adapter.Fill(aTable);
 
            return aTable;
        }
    }
}

The public, static method "GetADataTable" obtains the connection string from the "App.config" file. It takes a SqlCommand input and calls SQL Server to obtain a DataTable. For each method in the comparison, we will prepare a SqlCommand object and use this method to send the data to SQL Server.

Passing Data as Text Separated by Special Characters

The following code is used for testing passing data as Text to the Stored Procedure. Each data item is separated by a special character. This method of communicating with Stored Procedures has been widely used in the good old days for the last couple of years. The choice of separating characters in this article is different from most articles introducing this technique. To avoid the need to escape the data content passing to the Stored Procedure, two ASCII codes, 0 and 1, are used as the separators. These two ASCII codes are not printable, so they will not be confused with any string or integer content in the data.

C#
private DataTable TestWithTextType()
{
       char RowSeparator = System.Convert.ToChar(0);
       char ItemSeparator = System.Convert.ToChar(1);
  
       StringBuilder SB = new StringBuilder();
       foreach (DataRow row in DataToUpload.Rows)
       {
           int ID = System.Convert.ToInt32(row["ID"]);
           string Name = System.Convert.ToString(row["Name"]);
           int Score = System.Convert.ToInt32(row["Score"]);
  
           SB.Append(ID.ToString());
           SB.Append(ItemSeparator);
           SB.Append(Name);
           SB.Append(ItemSeparator);
           SB.Append(Score.ToString());
           SB.Append(RowSeparator);
       }
  
       SqlCommand cmd = new SqlCommand();
       cmd.CommandText = "UploadByTextType";
       cmd.Parameters.Add("@DataInTextType", 
                          SqlDbType.Text).Value = SB.ToString();
       cmd.CommandType = CommandType.StoredProcedure;
       DataTable aTable = SQLServerUtility.GetADataTable(cmd);
  
       return aTable;
}

The data to upload comes from the global variable "DataToUpload" which references the single instance of the class "DataSourceToUpload" mentioned earlier. The Stored Procedure that receives the data is generated by the following script:

SQL
IF  EXISTS (SELECT * FROM sys.objects WHERE 
    object_id = OBJECT_ID(N'[dbo].[UploadByTextType]') AND 
    type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UploadByTextType];
GO
 
CREATE PROCEDURE [dbo].[UploadByTextType]
 @DataInTextType AS TEXT
AS
BEGIN
 DECLARE @T AS TABLE([ID] INT, [Name] VARCHAR(250), [Score] INT)
 DECLARE @RowSeparator AS INT; DECLARE @ItemSeparator AS INT;
 DECLARE @P AS INT
 DECLARE @DataLength AS INT
 DECLARE @Item AS INT
 DECLARE @C AS VARCHAR(1)
 DECLARE @ID AS VARCHAR(20)
 DECLARE @Name AS VARCHAR(250)
 DECLARE @Score AS VARCHAR(20)
 
 SET @RowSeparator = 0; SET @ItemSeparator = 1
 
 SET @P = 1; SET @Item = 1; SET @ID = ''; SET @Name = ''; SET @Score = ''
 SET @DataLength = DATALENGTH(ISNULL(@DataInTextType, ''));
 
 WHILE @P  <= @DataLength
 BEGIN
  SET @C = SUBSTRING(@DataInTextType, @P, 1)
  
  IF ASCII(@C) = @RowSeparator BEGIN
   INSERT INTO @T VALUES(CAST(@ID AS INT), @Name, CAST(@Score AS INT));
   SET @Item = 1; SET @ID = ''; SET @Name = ''; SET @Score = '';
  END ELSE
  BEGIN
   IF @Item = 1 
    BEGIN IF ASCII(@C) = @ItemSeparator BEGIN SET @Item = @Item + 1 END 
          ELSE BEGIN SET @ID = @ID + @C END END
   ELSE IF @Item = 2
    BEGIN IF ASCII(@C) = @ItemSeparator BEGIN SET @Item = @Item + 1 END 
          ELSE BEGIN SET @Name = @Name + @C END END
   ELSE IF @Item = 3
    BEGIN IF ASCII(@C) = @ItemSeparator BEGIN SET @Item = @Item + 1 END 
          ELSE BEGIN SET @Score = @Score + @C END END
  END
  
  SET @P = @P + 1
 END;
 
 SELECT * FROM @T
END;

GO

This Stored Procedure parses the data from the ADO.NET client and restores it into a temporary table, and returns it to the calling ADO.NET client.

Passing Data in XML Format

With the introduction of the XML type in SQL Server 2005, we can send structured data to Stored Procedures as XML. The following is the C# code at the client side:

C#
private DataTable TestWithXMLType()
{
    System.Text.UTF8Encoding AEncoder = new UTF8Encoding();
    System.IO.MemoryStream ms = new System.IO.MemoryStream();
    System.Xml.XmlTextWriter tw = 
      new System.Xml.XmlTextWriter(ms, new System.Text.ASCIIEncoding());
 
    tw.WriteStartDocument();
    tw.WriteStartElement("DATAS");
 
    foreach (DataRow row in DataToUpload.Rows)
    {
        int ID = System.Convert.ToInt32(row["ID"]);
        string Name = System.Convert.ToString(row["Name"]);
        int Score = System.Convert.ToInt32(row["Score"]);
 
        tw.WriteStartElement("DATA");
        tw.WriteElementString("ID", ID.ToString());
        tw.WriteElementString("Name", Name);
        tw.WriteElementString("Score", Score.ToString());
        tw.WriteEndElement();
    }
 
    tw.WriteFullEndElement();
    tw.WriteEndDocument();
    tw.Flush();
    tw.Close();
 
    string strXML = AEncoder.GetString(ms.ToArray());
 
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "UploadByXMLType";
    cmd.Parameters.Add("@DataInXMLType", SqlDbType.Text).Value = strXML;
    cmd.CommandType = CommandType.StoredProcedure;
    DataTable aTable = SQLServerUtility.GetADataTable(cmd);
 
 
    return aTable;
}

The Stored Procedure is generated by the following script:

SQL
IF  EXISTS (SELECT * FROM sys.objects WHERE 
   object_id = OBJECT_ID(N'[dbo].[UploadByXMLType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UploadByXMLType];
GO
 
CREATE PROCEDURE [dbo].[UploadByXMLType]
 @DataInXMLType AS TEXT
AS
BEGIN
 DECLARE @I int
 DECLARE @XML AS XML
 
 SET @XML = CAST(@DataInXMLType AS XML)
 EXEC sp_xml_preparedocument @I OUTPUT, @XML
 
 SELECT *
 FROM OPENXML (@I, '/DATAS/DATA', 1) WITH
 ([ID] INT 'ID', [Name] VARCHAR(250) 'Name', [Score] INT 'Score') 
 
 EXEC sp_xml_removedocument @I
END;
GO

Passing Data as Table Valued Parameters

With the introduction of Table Valued Parameters in SQL Server 2008, we can pass an ADO.NET DataTable to SQL Server as a Stored Procedure parameter. The following is the client side .NET code. Although the data saved in the singleton class "DataSourceToUpload" is a DataTable by itself, the following code regenerates a DataTable. In real world applications, the data to be sent may not always in the DataTable format. Counting the time spent on generating the DataTable makes the comparison more practically meaningful.

C#
private DataTable TestWithSQLTableType()
{
    DataTable TableToUpload = new DataTable();
    TableToUpload.Columns.Add("ID", 
         System.Type.GetType("System.Int32"));
    TableToUpload.Columns.Add("Name", 
         System.Type.GetType("System.String"));
    TableToUpload.Columns.Add("Score", 
         System.Type.GetType("System.Int32"));
 
    foreach (DataRow row in DataToUpload.Rows)
    {
        int ID = System.Convert.ToInt32(row["ID"]);
        string Name = System.Convert.ToString(row["Name"]);
        int Score = System.Convert.ToInt32(row["Score"]);
 
        DataRow aNewRow = TableToUpload.NewRow();
        aNewRow["ID"] = ID;
        aNewRow["Name"] = Name;
        aNewRow["Score"] = Score;
 
        TableToUpload.Rows.Add(aNewRow);
    }
 
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "UploadByTableType";
    cmd.Parameters.AddWithValue("@DataInTableType", TableToUpload);
    cmd.CommandType = CommandType.StoredProcedure;
    DataTable aTable = SQLServerUtility.GetADataTable(cmd);
 
    return aTable;
}

On the SQL Server side, we need to first create a user defined Table type and use it as the Stored Procedure parameter type.

SQL
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[UploadByTableType]') 
  AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UploadByTableType];
GO
 
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss 
  ON st.schema_id = ss.schema_id 
 WHERE st.name = N'TStudentScore' AND ss.name = N'dbo')
DROP TYPE [dbo].[TStudentScore];
GO
 
CREATE TYPE [dbo].[TStudentScore] AS TABLE(
 [ID] [int] NULL,
 [Name] [varchar](250) NULL,
 [Score] [int] NULL
);
GO
 
CREATE PROCEDURE dbo.UploadByTableType
 @DataInTableType AS TStudentScore READONLY
AS
BEGIN
 SELECT * FROM @DataInTableType
END;
GO

Run the Test Application

To run the test application, you will need an instance of SQL Server 2008, and you will need enough permissions to run the above SQL scripts to generate the Stored Procedures and the user defined Table type.

You will also need to check the content in the application's "App.config" file.

XML
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>

<add key="SQLServerConnectionString" 
value="Data Source=localhost;Initial Catalog=Experiment;Integrated Security=SSPI;"/>

<add key="NoOfRowsOfDataInTheTest"
value="100000"/>
</appSettings>
</configuration>

Two values need to be configured. One is the connection string to connect to SQL Server. You will need to make sure the connection string has enough credentials to access the Stored Procedures and the user defined Table type. In my experiment, the login in the connection string is the server administrator, so I have unlimited access. The other value to configure is the number of rows of test data used for the comparison. I set this value to 100000 in my experiment.

If you have Visual Studio 2008, you can download the source code and compile it to run. If you do not have Visual Studio, the article comes with a compiled EXE. You can simply change the application's configuration file and run this EXE program.

Averaging the time spent by each method 10 times, I get the following result:

MethodTime Spent (seconds)
Pass as Text separated by special characters33
Pass as XML8
Pass as DataTable0.89

The difference is very impressive. Passing as XML is over 4 times faster than passing as Text separated by special characters, while passing as DataTable is almost 10 times faster than passing as XML. The following picture shows the result when uploading the data in DataTable format.

Experiment Result

What Makes the Difference so Big?

To answer this question, I did another experiment. I changed the three Stored Procedures by issuing the following SQL script, so the Stored Procedures do not parse the information but make an immediate return.

SQL
ALTER PROCEDURE [dbo].[UploadByTextType]
 @DataInTextType AS TEXT
AS
BEGIN
 -- Without processing the data passed over, simply return a simple
 -- Recordset
 SELECT 'A' AS A
END;
GO
 
ALTER PROCEDURE dbo.UploadByTableType
 @DataInTableType AS TStudentScore READONLY
AS
BEGIN
 -- Without processing the data passed over, simply return a simple
 -- Recordset
 SELECT 'A' AS A
END;
GO
 
ALTER PROCEDURE [dbo].[UploadByXMLType]
 @DataInXMLType AS TEXT
AS
BEGIN
 -- Without processing the data passed over, simply return a simple
 -- Recordset
 SELECT 'A' AS A
END;
GO

Repeating the experiment, I get the following result:

MethodTime Spent (seconds)
Pass as Text separated by special characters0.2
Pass as XML0.43
Pass as DataTable0.57

This result is not surprising. The length of the XML string is longer than the length of the text string for the same amount of information, and the DataTable is a more complex object than an XML string. Summarizing the results from the two experiments, we can come to the following conclusion:

  1. The time spent to send information to SQL Server alone is relatively short compared to the time for the Stored Procedures to process the information to an easy to use form. In this article, we programmed the Stored Procedures to transform the information into a temporary table or equivalent when necessary, which is the most common form in practice.
  2. Sending structured data in a form that is easy for the Stored Procedure to use, such as XML, and particularly DataTable, we can significantly improve the overall application performance.

More on Passing Data as Table Valued Parameters

The huge performance advantage by passing structured data as a DataTable to the Stored Procedures is demonstrated. But this is not the end of the story. If we create a new login to the SQL Server database, assign execution permission to the Stored Procedures, and change the "App.config" to use this login, the application fails immediately when sending the DataTable to the database. This is because we created a user defined Table type, and we will need to the grant execution permission to this user defined type to the login. I am not sure why Microsoft added this level of security. It seems to be an unnecessary maintenance penalty when we want to work in an efficient way.

Conclusion

This article made a comparison on the time spent to pass structure data to SQL Server stored procedures by different methods. We can find that the performance difference is very impressive if we want the information to be transformed into an easy to use form in the Stored Procedure. When data volume is small, this difference should not affect the overall application significantly. When the data volume becomes larger, a proper choice of the methods becomes necessary.

Performance is never the only reason when choosing among different methods, but it definitely deserves to be one of the reasons, and the choice is yours.

Limitations of this Experiment

It seems that this experiment has answered my questions and my choice is made, but it still has limitations.

  1. My entire test is performed on a development SQL Server located on the same computer as the client program, which is rarely the case in a production environment. What would the result be when the client and server are located on different computers?
  2. Are all my implementations for the methods the most efficient implementations?

To answer these questions, further tests are needed. If you are interested in conducting more extensive tests, I am eager to know your findings, and your feedback is appreciated.

History

This is the first revision of this article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Comments and Discussions

 
QuestionWhich aproach to use Pin
Member 250201321-Feb-12 1:29
Member 250201321-Feb-12 1:29 
AnswerRe: Which aproach to use Pin
Dr. Song Li21-Feb-12 2:27
Dr. Song Li21-Feb-12 2:27 
GeneralMy vote of 5 Pin
Syed Saqib Ali Tipu18-Jan-11 20:37
Syed Saqib Ali Tipu18-Jan-11 20:37 
GeneralNumbers Table Pin
aureolin12-Jan-10 6:40
aureolin12-Jan-10 6:40 
GeneralRe: Numbers Table Pin
Dr. Song Li25-Jan-10 17:22
Dr. Song Li25-Jan-10 17:22 
GeneralSorry, argument seems invalid to me Pin
JasonShort30-Dec-09 10:56
JasonShort30-Dec-09 10:56 
You are only comparing the time it takes to get the data in and return the same thing. How about actually doing something like an insert?

CREATE PROCEDURE dbo.UploadByTableType
 @DataInTableType AS TStudentScore READONLY
AS
BEGIN
 SELECT * FROM @DataInTableType
END;
GO


That is basically saying it is 10x faster to go in and say return than it is to do a bunch of logic and touch every row. Put a loop on that proc that has to go through and touch each row the way the other two methods do. Put a 1 on the end of each, anything to ensure that each row is touched.

Jason S Short, Ph.D.
VistaDB Software, Inc.

GeneralRe: Sorry, argument seems invalid to me Pin
Dr. Song Li31-Dec-09 4:10
Dr. Song Li31-Dec-09 4:10 
GeneralRe: Sorry, argument seems invalid to me Pin
fera4-Jan-10 2:58
fera4-Jan-10 2:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.