How to do IN statement as a parameter

Question

How to do IN statement as a parameter

Sean Grebey

Ten Centuries

Points: 1159
More actions
December 19, 2015 at 12:02 pm

#399488

I'm trying to convert some dynamic SQL in .NET into a Stored Procedure. What is the best way to pass a list of IDs to an SP as a parameter, e.g.
SELECT * FROM MyTable WHERE MyID IN(1,5,7)
SELECT * FROM MyTable WHERE MyID IN(1,3,5,7)
SELECT * FROM MyTable WHERE MyID IN(7)
The list of IDs can be variable length.
Thanks.
Sean

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

Sean Grebey (12/19/2015)
I'm trying to convert some dynamic SQL in .NET into a Stored Procedure. What is the best way to pass a list of IDs to an SP as a parameter, e.g.
SELECT * FROM MyTable WHERE MyID IN(1,5,7)
SELECT * FROM MyTable WHERE MyID IN(1,3,5,7)
SELECT * FROM MyTable WHERE MyID IN(7)
The list of IDs can be variable length.
Thanks.
Sean

Something along these lines should do the trick nicely...

DECLARE @ParameterArray VARCHAR(8000) = '1,3,5,7';

SELECT

t.Col1,

t.Col2,

t.Col3

FROM

dbo.MyTable t

WHERE

EXISTS (SELECT 1 FROM dbo.DelimitedSplit8K(@ParameterArray, ',') ds WHERE t.MyID = CAST(ds.Item AS INT))

You can find the code for the DelimitedSplit*K here: http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Or use a table-type parameter and use it in an IN subquery

WHERE ID IN (SELECT ID FROM @TableTypeParameter)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Piling on, while the string splitting method works fine when working with small number of values the performance degrades very quickly when the number of values increases. If you expect hundreds or more values being passed to procedure then you should seriously look into either a table type parameter as Gail suggested or even XML type parameter, both methods can handle very large number of values much better than the string splitting method.

😎

Quick XML example

USE tempdb;

GO

SET NOCOUNT ON;

GO

DECLARE @OBJECTS XML = '<OBJECTS><OBJ ID="5"/><OBJ ID="19"/><OBJ ID="27"/></OBJECTS>';

SELECT

SOBJ.*

FROM sys.objects SOBJ

WHERE SOBJ.object_id IN

(

SELECT

OBJ.DATA.value('@ID','INT')

FROM @OBJECTS.nodes('OBJECTS/OBJ') AS OBJ(DATA)

);

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

Eirikur Eiriksson (12/20/2015)
Piling on, while the string splitting method works fine when working with small number of values the performance degrades very quickly when the number of values increases.

It's uncharacteristic of you to make a performance claim with no demonstrable test. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 5

Jeff Moden (12/20/2015)
Eirikur Eiriksson (12/20/2015)
Piling on, while the string splitting method works fine when working with small number of values the performance degrades very quickly when the number of values increases.
It's uncharacteristic of you to make a performance claim with no demonstrable test. 😉

There surely is a lot of performance tests with the suggested splitter. But, it should at least be noted there will be a performance decrease with any UDF, data conversions, etc. But splitter-to-splitter, the DelimitedSplit8K surely seems like the way to go.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

xsevensinzx (12/20/2015)
But, it should at least be noted there will be a performance decrease with any UDF, data conversions, etc. But splitter-to-splitter, the DelimitedSplit8K surely seems like the way to go.

I appreciate the kudo for DelimitedSplit8k but there's no performance hit just because you use a UDF [font="Arial Black"]IF [/font]it's a properly formed and written iTVF. Please see the following article for proof of that in the form of a performance test.

http://www.sqlservercentral.com/articles/T-SQL/91724/

I also haven't tested DelimitedSplit8K against already formed XML. It's only been tested against XML that was created from a CSV, which requires expansion of the string both by replacement and concatenation, which can be a killer for performance. There is the possibility that XML already in the correct form might split out faster. Of course, that's totally ignoring the huge penalty to IO from the nearly obscene tag bloat, especially in this case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

Jeff Moden (12/20/2015)
Eirikur Eiriksson (12/20/2015)
Piling on, while the string splitting method works fine when working with small number of values the performance degrades very quickly when the number of values increases.
It's uncharacteristic of you to make a performance claim with no demonstrable test. 😉

Sorry for the lack of test harness, will provide one as soon as I have the time. Recently encountered this exact issue so it will be easy to dig up the work;-)

😎

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

Eirikur Eiriksson (12/20/2015)
Jeff Moden (12/20/2015)
Eirikur Eiriksson (12/20/2015)
Piling on, while the string splitting method works fine when working with small number of values the performance degrades very quickly when the number of values increases.
It's uncharacteristic of you to make a performance claim with no demonstrable test. 😉
Sorry for the lack of test harness, will provide one as soon as I have the time. Recently encountered this exact issue so it will be easy to dig up the work;-)
😎

Very cool. Rumor has it that the fully formed XML actually will be faster according to an article (mislaid the URL, unfortunately) that Phil Factor wrote up on Simple-Talk. When I originally read the article (year or two ago?), I didn't have the time to prove it one way or the other myself and then I forgot about it.

Thanks for your time, Eirikur. I'll see if I can resurrect Phil's article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

Ah... never mind, Eirikur. I found Phil's old article. My memory was correct. [font="Arial Black"]IF [/font]you pass the parameters as well formed XML as Phil discovered, you can achieve performance greater than DelimitedSplit8k not to mention not being limited to just 8K Bytes. Here's Phil's good article...

https://www.simple-talk.com/blogs/2012/01/05/using-xml-to-pass-lists-as-parameters-in-sql-server/

I've not actually verified Phil's test harness nor whether he's using grooved data or not (which makes a HUGE difference on such tests) but he's usually pretty good about such things. I'll verify that in the near future.

If, however, you pass CSV and convert it to XML and then shred it, XML loses badly every time. You already know where that article is but here's the link if others want to check it.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

I also have an article that I'm trying to put the finishing touches on about how the use of "grooved" data can actually cause folks to select the worst performing code instead of the best, which many even well known authors and "experts" have mistakenly posted in a multitude of articles and other discussions making it erroneously look like DelimitedSplit8K isn't any better or is slightly worse than an inline "convert to XML and shred it" solution.

Of course, if one writes and exercises a properly written SQLCLR splitter to do the task, you don't need either. It's a real shame that T-SQL doesn't have a real machine-language-level splitter built in already.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 10

Sean Grebey

Ten Centuries

Points: 1159

December 20, 2015 at 1:34 pm

#1847001

Thanks for the help guys.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 11

Jeff,

what I've found is that there are two thresholds, one for the well formed XML and other for the CSV to XML, the former is lower than the latter, partly to do with the memory allocation and incorrect cardinality estimation for the DelimitedSplit8K function. For the latter, when reaching 100K+ parameters which obviously means an DelimitedSplitMax version, the impeded penalty of the MAX size is already a huge handicap. As we know, 8K and MAX versions are two different things and once one enters into the "huge" range, the playing field shifts drastically.

The lower threshold seems to largely depend on the system's resources, proper format of the XML input and last but not least, the actual transfer time of the parameter values over the network, the XML overhead in terms of size can easily downplay any advantages in the parsing.

I did use Dwain Champ's idea on shortcutting (a thread here about 6 months ago) directing the input to two different streams to solve my problem but I'm not entirely satisfied with the solution.

Sorry again for my hurried and sparse response, will be back with more substantial material as soon as I have the time.

😎

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

Interestingly enough, I think that anyone passing 100K parameters to a stored procedure is 1) doing something wrong or 2) doing something really wrong. 😀 There are lot's of reasons to pass 100K items to SQL Server (a simple file import is just such an example) but they probably and usually shouldn't be passed as parameters.

I went through that with one small company that I help every now and again. They were using a very small array to drive the creation of more than a quarter million rows of data and then passing all that to SQL Server. I showed them how to pass the small array of data to SQL Server and let it do the work. It took the job time down from hours to just seconds. They had two comments... "It couldn't have run correctly... it didn't take long enough" and after I explained what I did (turned out to be a relatively simple set of CROSS JOINs) and proved that it worked, the conversation turned to "We didn't know you could do such a thing in SQL Server".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Henrik Staun Poulsen SSCertifiable Points: 6583 More actions · Answer 13

Hi Sean,

May I suggest that you take a look at http://sommarskog.se/

His section on dynamic search conditions is long, but worth-while reading.

Best regards,

Henrik

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 14

Jeff Moden (12/20/2015)
Interestingly enough, I think that anyone passing 100K parameters to a stored procedure is 1) doing something wrong or 2) doing something really wrong. 😀 There are lot's of reasons to pass 100K items to SQL Server (a simple file import is just such an example) but they probably and usually shouldn't be passed as parameters.

Totally agree but unfortunately one gets one of those "design hot potato" landing on ones desk once in a while.

I went through that with one small company that I help every now and again. They were using a very small array to drive the creation of more than a quarter million rows of data and then passing all that to SQL Server. I showed them how to pass the small array of data to SQL Server and let it do the work. It took the job time down from hours to just seconds. They had two comments... "It couldn't have run correctly... it didn't take long enough" and after I explained what I did (turned out to be a relatively simple set of CROSS JOINs) and proved that it worked, the conversation turned to "We didn't know you could do such a thing in SQL Server".

Done similar by persisting the likes of hierarchy nodes, product ids etc. in a table and then only having to pass a single key value rather than the whole shebang, execution times of course dropping from minutes to milliseconds, let alone not having to compress the array of values when bouncing it between application servers and database servers.

😎