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
