SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a Variable for an IN Predicate


Using a Variable for an IN Predicate

Author
Message
Bruce Dow
Bruce Dow
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 188
Thanks for article. Good reminder.
Your Name Here
Your Name Here
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 829
@tfifield

I apologize for not replying to your question. I was in the middle of rolling out our production SAP implementation at corporate when I posted and, well, let's just say I was a bit distracted.

I haven't benchmarked the performance but I'll defend my laziness by saying I put the code I write into one of two buckets: a "I run this occasionally to get DBA-related info or for troubleshooting" and "This is executed hundreds/thousands of times a day by end users".

This code sort of falls into the first bucket. I've used this XML parsing stunt quite a bit (I truly wish I could remember where I saw it first and give thanks to the original author genius that wrote it) and for small sets of parsed data it works famously.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60198 Visits: 17952
Irozenberg 1347 (3/1/2013)
Elementary sub-query or EXISTS clause would
Radically improve performance.
If you like fancy coding you could pass XML as
parameter and use XQUERY.


You should read the article about the splitter. It will blow the doors off an XML parser. You can find the article by following the link in my signature about splitting strings. The article is great but the ensuing discussion (although incredibly lengthy) is even better.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207278 Visits: 41961
Irozenberg 1347 (3/1/2013)
Elementary sub-query or EXISTS clause would
Radically improve performance.
If you like fancy coding you could pass XML as
parameter and use XQUERY.


Since you made a claim of performance, let's see your "elementary sub-query or EXISTS" clause.

I do agree that passing (ugh!) XML would also be fast but it has no respect for the "pipe".

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207278 Visits: 41961
Sean Lange (3/1/2013)
Irozenberg 1347 (3/1/2013)
Elementary sub-query or EXISTS clause would
Radically improve performance.
If you like fancy coding you could pass XML as
parameter and use XQUERY.


You should read the article about the splitter. It will blow the doors off an XML parser. You can find the article by following the link in my signature about splitting strings. The article is great but the ensuing discussion (although incredibly lengthy) is even better.


Be careful now. He's not talking about passing a CSV and using an XML parser to split it. He's talking about passing actual XML and parsing it, which is actually very fast.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60198 Visits: 17952
Jeff Moden (3/1/2013)
Sean Lange (3/1/2013)
Irozenberg 1347 (3/1/2013)
Elementary sub-query or EXISTS clause would
Radically improve performance.
If you like fancy coding you could pass XML as
parameter and use XQUERY.


You should read the article about the splitter. It will blow the doors off an XML parser. You can find the article by following the link in my signature about splitting strings. The article is great but the ensuing discussion (although incredibly lengthy) is even better.


Be careful now. He's not talking about passing a CSV and using an XML parser to split it. He's talking about passing actual XML and parsing it, which is actually very fast.


Ahh I guess I misread that. Blush

It is fast on the sql side of things but I have never really been convinced that applications gain much benefit because of all the extra data that has to be passed.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207278 Visits: 41961
Sean Lange (3/1/2013)

It is fast on the sql side of things but I have never really been convinced that applications gain much benefit because of all the extra data that has to be passed.


+1000 to that!

Shredding an XML parameter takes a bit fewer resources than parsing a CSV with DelimitedSplit8K and is even a bit faster. An XML parameter also allows you to easily break the 8k barrier. Heh... of course, with XML bloat, you almost have to. :-D

The other thing that people fail to consider is all the other resources involved. Like the "pipe". It's much more expensive on the pipe to pass XML than it is CSV or TSV. It's also more expensive on the I/O of the server. Being I/O bound is much more difficult to fix than optimizing some code.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jimmyzimms
jimmyzimms
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 14
A standardized xml type containing the set (think something installed in your schemas collection) would be far easier to use and debug plus SQL server is a stud at using XML. Unless you're running huge inputs regularly for your IN clause, you really don't need to probably micro-optimize
Bruce W Cassidy
Bruce W Cassidy
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2991 Visits: 1033
Passing XML as the parameter is actually another good use-case for a similar technique, and I've done this where I was dealing with an object-level interface. In that way, the parameter can be passed an entire object worth of data, or for that matter, a set of objects, which can have parent-child relationships encoded within the XML (I used this to pass sets of invoices around between the data layer in the middle tier and the database).

However, that's not where I have used the technique of comma-delimited strings as parameters; that's more about working with reporting services. I've scratched my head a bit, but I can't think of another case where I've passed sets of values as a delimited string. I have seen it used to express lists of values in a "parameter" table, but I think that's just silliness and not a valid reason to use this technique (yes, we are allowed to have tables with relationships!)

I will admit that once I am in T-SQL, if I have to pass sets around as parameters, I will do it either within a table or as a table-variable. If I only ever lived in SQL Server (or Oracle), I wouldn't need to be passing sets as delimited strings.

I wonder what TRON would have looked like if it were based on an RDBMS rather than a game?

My other thought was that SQL as a language is based around working with sets of data. It shouldn't matter too much about how that data is encoded; whether it's XML, a delimited string, a file, a spread sheet or a table within the database, I still want to be able to apply the same capabilities of SQL to it. From that point of view, taking a comma-delimited string and expressing it as a table is perfectly valid.
Bruce W Cassidy
Bruce W Cassidy
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2991 Visits: 1033
jimmyzimms (3/1/2013)
A standardized xml type containing the set (think something installed in your schemas collection) would be far easier to use and debug plus SQL server is a stud at using XML.

Reporting Services will only automatically generate a comma-delimited string for multi-value parameters; it won't generate XML. So I would have to write a parser in Reporting Services to express the parameters in XML before passing them to T-SQL where I then have to break the XML down again.

Neither of those is actually difficult, but I don't see the value in spending the extra time every time I call a stored procedure from SQL Server Reporting Services to ensure that any muti-value parameters are encoded in XML.

In this case, it would make the interface between reporting services and SQL Server harder to use or debug.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search