Transcript Passing multi-value parameters to MS Sql Server

Why would we do this?

SELECT rows based on a range of
values

Filter reports based on user selection

Deleting a series of rows

Bulk INSERT/UPDATE
Approaches

Looping

Pass delimited string and parse with a
Split implementation

Pass XML fragment (SQL 2005+)

User Defined Table Types (SQL 2008+)
Looping
var results = new List<T>();
foreach (string val in valuesToLookFor) {
// Do data access stuff
results.Add(someQueryResult)
}
Delimited and Split
List<T> results;
string values = “a, b, c, d, e”;
// Data access setup
// Pass in values as param to sproc
results = //Read results from DataReader
Delimited and Split (cont.)
CREATE PROCEDURE dbo.p_Table_Read (
@Values VARCHAR(50)
)
AS
SELECT t.Column1, t.Column2
FROM
dbo.Table t
JOIN dbo.Split(@Values) s
ON s.value = t.Column4
XML Fragment
List<T> results;
string values = “<Root><Row val=‘a’
/><Row val=‘b’ /><Row val=‘c’ /></Root>”;
// Data access setup
// Pass in values as param to sproc
results = //Read results from DataReader
XML Fragment (cont.)
CREATE PROCEDURE dbo.p_Table_Read (
@Values XML
)
AS
SELECT t.Column1, t.Column2
FROM
dbo.Table t
JOIN @Values.nodes(‘/Root/Row’) v(n)
ON v.n.value(‘@val’, VARCHAR(25))
= t.Column4
User Defined Table Type
CREATE TYPE dbo.MyUddt AS TABLE (
Value VARCHAR(100) NOT NULL
)
User Defined Table Type (cont.)
List<T> results;
DataTable values = myValuesTable;
// Data access setup
// Pass in values as param to sproc
param = new SqlParameter(“@Values”,
SqlDbType.Structured).Value = values;
param.TypeName = “dbo.MyUddt”;
results = //Read results from DataReader
User Defined Table Type (cont.)
CREATE PROCEDURE dbo.p_Table_Read (
@Values dbo.MyUddt READONLY
)
AS
SELECT t.Column1, t.Column2
FROM
dbo.Table t
JOIN @Values v
ON v.value = t.Column4
Performance
Test Setup

My machine:






Intel i5-2500 @ 3.30 GHz (quad-core)
8GB RAM
160GB Intel SSD (SSDSA2BW160G3H)
Windows 7 64-bit w/ SP1
SQL 2008 R2 w/ SP1
Test runs:
 Each test is primed
 Row counts = 1, 10, 50, 100, 500, 1000, 5000,
10000, 5000
Test Setup (cont.)





Values for each run were determined at
random
All tests run against local copy of Tlink
Dental from Dev
All sprocs used are identical with exception
of input parameter
Each sproc does an index seek and key
lookup against a NCI on dbo.Patient
Entity Framework 4.3 was used for data
access
Overall Results
100
90
80
Total Seconds
70
Split8k
60
SplitClr
SplitTLink
50
Uddt
40
Xml
Looping EF 4.3
30
Looping ADO
20
10
0
1
10
50
100
500
1000
Number of SourceIds
5000
10000
50000
Overall without Loops
0.5
0.45
0.4
Total Seconds
0.35
0.3
Split8k
SplitClr
0.25
SplitTLink
0.2
Uddt
Xml
0.15
0.1
0.05
0
1
10
50
100
500
1000
Number of SourceIds
5000
10000
50000
Split Functions
0.03
0.025
Total Seconds
0.02
Split8k
0.015
SplitClr
SplitTLink
0.01
0.005
0
1
10
50
100
500
1000
Number of SourceIds
5000
10000
50000
Links/References

A split strings comparison article: link

The SQL 8k CSV Splitter: link
(registration req.)

The SQL CLR splitter: link