Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some calculated values, and optionally update two columns on a 3rd table.

My application has a List<T> of records, and I'm trying to decide if I should loop through the list and run the procedure on each record, or combine the id values into a single string and make a single database call. If I make a single database call, then SQL would be responsible for converting the comma-delimited string to a temp table and running the scripts on all records at once.

I'm leaning towards passing a comma-delimited string of Ids to the procedure, but I am not sure if this is an ideal way of doing this kind of mass-update, or if I am missing some key factor that would affect this decision.

What should I consider when deciding if I should pass a comma-delimited string of ids to a stored procedure instead of calling it individually per record? Or is there a more efficient way to do this kind of update from an application?

I am using SQL Server 2005 and Entity Framework

share|improve this question

3 Answers 3

I ran 2 tests for a comma-delimited list as well as a table variable type. I took the first answer above and thought that table variable types were ok and have of course read by now that they are not valid for 2005. However, I think it can still be valuable information for this post. I took the original post requirements literally and assumed updating 100,000 records. The first test the data was submitted from vb.net using a comma-delimited list. For each test, I started a timer in vb.net right before calling ExecuteNonQuery and stopped it when it returned to code (ie. I did not test the time it took to build the list, although this was trivial, less than a second, in both samples).

The comma-delimited list consistently took between 96 and 98 seconds to complete the 100,000 record update. The second test used the table variable type (SQL 2008 and greater). When submitting 100,000 records and updating a table, it consistently took between .43 and .44 seconds. This is a considerable improvement over the list.

However, seeing as you have to use the comma-delimited list, I'll post the code I used to split the comma-delimited list to see if any performance improvements can be made. I hope it helps!

    CREATE FUNCTION [dbo].[fn_Split]
    (
       @sText VARCHAR(MAX), @sDelim VARCHAR(20) = ','
    )
    RETURNS @tbl TABLE (idx INT PRIMARY KEY, [Value] VARCHAR(MAX))
    AS
    BEGIN
       DECLARE @pos INT;
       DECLARE @piece VARCHAR(MAX);

       IF RIGHT(RTRIM(@sText), LEN(@sDelim)) <> @sDelim
          SET @sText = @sText + @sDelim;

       DECLARE @idx INT = 0;
       SET @pos =  PATINDEX('%' + @sDelim + '%' , @sText); --use PATINDEX (vs CHARINDEX) b/c the string to be searched can by VARCHAR(MAX)
       WHILE @pos > 0
       BEGIN
          SET @piece = LEFT(@sText, @pos - 1);
          INSERT INTO @tbl (idx, [Value]) VALUES (@idx, CAST(@piece AS VARCHAR(MAX)));

          SET @sText = STUFF(@sText, 1, @pos, '');
          SET @pos =  PATINDEX('%' + @sDelim + '%' , @sText);
          SET @idx += 1;
       END;

       RETURN

    END
    GO

Here is the test stored procedure I used to update a table with the submitted comma-delimited list.

    CREATE PROCEDURE [dbo].[UpdateList]
    @List VARCHAR(MAX)
    AS
    BEGIN

    SET NOCOUNT ON;

    UPDATE t1
    SET TestCol = 'ABC'
    FROM dbo.Table_1 t1
    INNER JOIN dbo.fn_split(@List, ',') AS tbl ON t1.ID = tbl.[Value]
    END

For completeness, here is the code I used to create the table variable type and the stored procedure which consumed it as a parameter.

    CREATE TYPE dbo.Table1Type AS TABLE(
       ID INT NOT NULL,
       TestCol VARCHAR(10) NULL);
    GO

    CREATE PROCEDURE dbo.UpdateT1Type
        @T Table1Type READONLY
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for procedure here
        --DECLARE @List VARCHAR(MAX) = '0,1,2'
        UPDATE t1
        SET TestCol = tbl.TestCol
        FROM dbo.Table_1 t1
        INNER JOIN @T AS tbl ON t1.ID = tbl.ID
    END
    GO

Also, here is the calling VB.NET code for reference.

   Private _Connection As SqlConnection = Nothing
   Private Function OpenConnection() As String
      CloseConnection()
      _Connection = New SqlConnection("Data Source=(local);Initial Catalog=MyTestDatabase;Integrated Security=True")
      _Connection.Open()

      Return True
   End Function

   Private Sub CloseConnection()
      If _Connection IsNot Nothing Then
         If _Connection.State <> ConnectionState.Closed Then
            _Connection.Close()
         End If
         _Connection = Nothing
      End If
   End Sub

   Private Sub SubmitCommaDelimitedList()
      Dim cdList As New StringBuilder

      For i As Integer = 0 To 100000
         cdList.Append(i.ToString + ",")
      Next
      'remove last comma
      cdList.Remove(cdList.Length - 1, 1)

      If OpenConnection() Then
         Dim cmd As SqlCommand = New SqlCommand()
         With cmd
            .Connection = _Connection
            .CommandType = CommandType.StoredProcedure
            .CommandText = "dbo.UpdateList"
            .CommandTimeout = 300
            .Parameters.AddWithValue("@List", cdList.ToString)
         End With

         Dim stopWatch As New Stopwatch()
         stopWatch.Start()

         cmd.ExecuteNonQuery()

         stopWatch.Stop()
         ' Get the elapsed time as a TimeSpan value.
         Debug.WriteLine(stopWatch.Elapsed.TotalSeconds.ToString("0.000000"))

         Call CloseConnection()
      End If

   End Sub

   Private Sub SubmitTableVar()
      Dim dt As DataTable = New DataTable("Items")
      dt.Columns.Add("ID", System.Type.GetType("System.Int32"))
      dt.Columns.Add("TestCol", System.Type.GetType("System.String"))
      For i As Integer = 0 To 100000
         dt.Rows.Add(i, "ABCD")
      Next

      If OpenConnection() Then
         Dim cmd As SqlCommand = New SqlCommand()
         With cmd
            .Connection = _Connection
            .CommandType = CommandType.StoredProcedure
            .CommandText = "dbo.UpdateT1Type"
            .CommandTimeout = 300
         End With
         Dim sqlParam As SqlParameter = cmd.Parameters.AddWithValue("@T", dt)
         sqlParam.SqlDbType = SqlDbType.Structured

         Dim stopWatch As New Stopwatch()
         stopWatch.Start()

         cmd.ExecuteNonQuery()

         stopWatch.Stop()
         ' Get the elapsed time as a TimeSpan value.
         Debug.WriteLine(stopWatch.Elapsed.TotalSeconds.ToString("0.000000"))

         Call CloseConnection()
      End If

   End Sub
share|improve this answer
    
After re-reading your post, I apologize for doing this in ADODB rather than EF, it was just faster to throw the test together that way. Also, I would also recommend the comma-delimited list rather than looping over the records individually as this almost always takes longer (but I'd have to test it to be sure :) –  Delux Aug 22 '12 at 21:54

Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.

I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works on 2005). The same strategy would work better for you in this situation but you may need to adapt the implementation based on the fact you are using the Entity Framework.

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

EDIT

As you rightly pointed out I am wrong about the fact this works on 2005 - sorry about that!

However, I have some alternate suggestions.

As SQL Server 2005 does support table variables (just not as parameters to stored procedures as you pointed out) you could parse the delimited string and insert the id's into a table variable. You could then use the table variable to perform a set-based update.

Alternatively the link below provides a different take on the same problem by persisting the values to a table first thereby avoiding serialization and de-serialization of the id values:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx

I hope this helps you.

share|improve this answer
1  
I thought table parameters for stored procedures wasn't supported in SQL Server 2005? From the article you linked, "Table Value Parameters is a new feature for developers in SQL Server 2008" –  Rachel Aug 22 '12 at 14:02

I had a similar problem in the past. I needed to send up to 100K numbers to SQL Server 2005 as fast as possible. After lots of benchmarks I think the fastest approach in my environment was to pack numbers in binary format. That needed less network packets and parsed very fast using very little CPU. Erland Sommarskog included the C# code I used to pack numbers into a CLOB in his article Arrays and Lists in SQL Server 2005 , in the section entitled "Passing Numbers as Binary".

share|improve this answer
    
That is a great article to read through for this, thank you :) –  Rachel Aug 22 '12 at 17:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.