Passing multi-value parameters to MS Sql Server

italiansaucyΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 5 μήνες)

80 εμφανίσεις

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

0
10
20
30
40
50
60
70
80
90
100
1
10
50
100
500
1000
5000
10000
50000
Total Seconds

Number of SourceIds

Split8k
SplitClr
SplitTLink
Uddt
Xml
Looping EF 4.3
Looping ADO
Overall without Loops

0
0.05
0.1
0.15
0.2
0.25
0.3
0.35
0.4
0.45
0.5
1
10
50
100
500
1000
5000
10000
50000
Total Seconds

Number of SourceIds

Split8k
SplitClr
SplitTLink
Uddt
Xml
Split Functions

0
0.005
0.01
0.015
0.02
0.025
0.03
1
10
50
100
500
1000
5000
10000
50000
Total Seconds

Number of SourceIds

Split8k
SplitClr
SplitTLink
Links/References


A split strings comparison article:
link



The SQL 8k CSV Splitter:
link

(registration req.)



The SQL
CLR splitter
:
link