Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Using a Variable for an IN Predicate Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 1, 2014 8:25 AM
Points: 253, Visits: 87
Thanks for article. Good reminder.
Post #1425499
Posted Friday, March 1, 2013 7:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:52 AM
Points: 141, Visits: 642
@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.
Post #1425502
Posted Friday, March 1, 2013 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1425504
Posted Friday, March 1, 2013 7:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425525
Posted Friday, March 1, 2013 8:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425528
Posted Friday, March 1, 2013 8:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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.

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 Moden's 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)
Post #1425536
Posted Friday, March 1, 2013 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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.

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425547
Posted Friday, March 1, 2013 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:19 PM
Points: 2, Visits: 7
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
Post #1425619
Posted Friday, March 1, 2013 10:31 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #1425622
Posted Friday, March 1, 2013 10:37 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #1425631
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse