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 Tuesday, June 21, 2011 8:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 1,945, Visits: 2,921
Some version of this mis-use of SQL gets invented again every few months. It is a slow, dangerous mess. Are you familar with the long parameters list idiom? SQL is a data base language, not a tool for writing parsers.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1129037
Posted Tuesday, June 21, 2011 11:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
irozenberg (6/20/2011)
Todd, thank you for article.
Did you try UDF that return a table?

Cheers

The Delimited8KSplit is a UDF that returns a table. The last example bypasses the creation of a temp table and uses the UDF in a CTE. I could have modified the UDF to return an INT and used it directly, but I thought it would be better just to use Jeff's UDF the way it was, convert to INT and use that.
Todd Fifield
Post #1129154
Posted Tuesday, June 21, 2011 11:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
Sarus-127369 (6/21/2011)
too keep it simple whith less code (but only for short in-lists)

Declare @inStr varchar(100)
Set @inStr = '1,2,4,6'


select * from testtable
where ','+@inStr+',' like '%,'+cast(column as varchar)+',%'



Saurus,
I've used that type of LIKE before. Sometimes it works just fine, but it doesn't scale very well and the examples I used all had indexes on the column. The LIKE operator with '%' on the left won't use indexes.
Todd Fifield
Post #1129157
Posted Tuesday, June 21, 2011 11:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
TheSQLGuru (6/21/2011)
You should absolutely positively NOT use the CTE for this scenario unless you are guaranteed to have VERY few rows split out AND you do not have any signficant data skew. If you have either or both of those you will get screwed with a bad plan at least some of the time. Either a nested loop index seek plan with a kajillion rows or a scan/hash plan with a few rows. The optimizer can't have any idea how many rows are coming out of the split. Nor can the optimizer have statistics on the VALUES of the row(s) coming out of the split.

Kevin,
I thought about that before I tested the CTE version. For the small number of table elements it worked just fine. As I stated in the article, testing should be done to check performance. I realize that CTE's don't have statistics.

In a production environment where it's likely that there could be many elements I probably wouldn't even bother to bench mark the CTE version. I just brought it up as a possibility.

Thanks for your comments. I appreciate your input.
Todd Fifield
Post #1129163
Posted Tuesday, June 21, 2011 11:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
Your Name Here (6/21/2011)
Hey Todd

Re: splitting a string to a table variable "array" - give this a try:

DECLARE @values TABLE (value VARCHAR(10))
DECLARE @xml AS XML,@str AS VARCHAR(100),@delimiter AS VARCHAR(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = CAST(('<X>'+REPLACE(@str,@delimiter ,'</X><X>')+'</X>') AS XML)
INSERT INTO @values (value)
SELECT N.value('.', 'varchar(10)') AS value FROM @xml.nodes('X') as T(N)
SELECT * FROM @values

Cheers,
Ken

PS I can't claim to have come up with this - I found it somewhere on the net.

Ken,
Thanks for your input. I'll give it a try.

Have you ever bench marked it against the technique I'm using?
Todd Fifield
Post #1129165
Posted Tuesday, June 21, 2011 11:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
CELKO (6/21/2011)
Some version of this mis-use of SQL gets invented again every few months. It is a slow, dangerous mess. Are you familar with the long parameters list idiom? SQL is a data base language, not a tool for writing parsers.

Joe,
I'm very familiar with the fact that SQL is not a language for writing parsers. I worked on an early word processor in PDP-11 assembly language and I've written 2 text editors in C. I've also had to do parsing in COBOL. Believe me, C is the language of choice for parsing.

However, a delimited array of parameters is a very common SQL problem. The idea is to make it easy to program and still be robust both in the front end and back end. I've seen this requirement many, many times and this technique is rock solid.

This technique, in any case, is NOT a parser. All of the parsing is done at the beginning, which converts the string array into a TABLE, which is what SQL Server handles best. That's the whole point of the article - let SQL Server do what it does best. Join to tables and such.
Todd Fifield
Post #1129182
Posted Tuesday, June 21, 2011 12:31 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:50 PM
Points: 95, Visits: 219
Why bother with the temp table? Why not just imbed the select into your where clause?

SELECT  
SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson
, COUNT(*) AS Orders
, SUM(SO.SubTotal) AS TotalAmount
FROM Sales.SalesOrderHeader SO
INNER JOIN HumanResources.Employee E ON
SO.SalesPersonID = E.EmployeeID
INNER JOIN Person.Contact C ON
E.ContactID = C.ContactID
WHERE SO.OrderDate BETWEEN @StartDate And @EndDate
-- The dynamic IN predicate.
AND SO.SalesPersonID IN (SELECT CONVERT(INT, Item) AS SalesPersonID
FROM dbo.DelimitedSplit8K(@SalesPeople, ','))
GROUP BY
SO.SalesPersonID, C.FirstName, C.LastName

Post #1129229
Posted Tuesday, June 21, 2011 12:44 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:50 PM
Points: 95, Visits: 219
Celko
Some version of this mis-use of SQL gets invented again every few months. It is a slow, dangerous mess. Are you familar with the long parameters list idiom? SQL is a data base language, not a tool for writing parsers.


Don't you just love people who like to shoot stuff down without providing an alternative to the problem at hand.

If it's such a misuse what is the solution? Write reports that only let the users select one item at a time? My users would consider that a "non-solution".
Post #1129245
Posted Tuesday, June 21, 2011 4:22 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
bphipps-931675 (6/21/2011)
Celko
Some version of this mis-use of SQL gets invented again every few months. It is a slow, dangerous mess. Are you familar with the long parameters list idiom? SQL is a data base language, not a tool for writing parsers.


Don't you just love people who like to shoot stuff down without providing an alternative to the problem at hand.

If it's such a misuse what is the solution? Write reports that only let the users select one item at a time? My users would consider that a "non-solution".

Thanks for backing me up on this.
Todd Fifield
Post #1129376
Posted Tuesday, June 21, 2011 4:32 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
bphipps-931675 (6/21/2011)
Why bother with the temp table? Why not just imbed the select into your where clause?

SELECT  
SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson
, COUNT(*) AS Orders
, SUM(SO.SubTotal) AS TotalAmount
FROM Sales.SalesOrderHeader SO
INNER JOIN HumanResources.Employee E ON
SO.SalesPersonID = E.EmployeeID
INNER JOIN Person.Contact C ON
E.ContactID = C.ContactID
WHERE SO.OrderDate BETWEEN @StartDate And @EndDate
-- The dynamic IN predicate.
AND SO.SalesPersonID IN (SELECT CONVERT(INT, Item) AS SalesPersonID
FROM dbo.DelimitedSplit8K(@SalesPeople, ','))
GROUP BY
SO.SalesPersonID, C.FirstName, C.LastName


It very well could have been done that way. The main reason I'm using a temp table, aside from it being easy to read and people are used to them, was that temp tables have statistics. I also wanted to demonstrate the the various ways it could be done easily and thought going to a temp table would be easier to read and understand in the various scenarios.

Kevin pointed out that the CTE version would probably not scale very well since CTE's don't have statistics. I'm not sure that a table valued function is really any better than a CTE on creating statistics. I simply don't know. Maybe some guru like Paul White could comment on this.

At the very least, I do know that temp tables have statistics and that a long array that would produce many records in the temp table would scale upwards properly. In fact if it got huge, it could be indexed if it came to that. I don't normally index temp tables unless I get more back in performance than I lose in the indexing phase, however.
Todd Fifield
Post #1129380
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse