June 21, 2011 at 12:31 pm
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
June 21, 2011 at 12:44 pm
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".
June 21, 2011 at 4:22 pm
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
June 21, 2011 at 4:32 pm
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
June 21, 2011 at 10:17 pm
There is an alternative. Read: http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL
June 22, 2011 at 5:11 am
vanslly (6/21/2011)
When I originally read this article (on SQLServerCentral) I thought it may discuss the topic of the article you have linked to (table valued parameters).
I think that TVPs are great, but you can't always get the data in a table format from your calling application. The article linked above even states that.
And the linked article even shows what to do if you can't - which seems to be about the same as described in this article on SSC - receive a list - split it up - join to it, etc. (this is described at this part of the linked article: http://www.sommarskog.se/arrays-in-sql-2005.html#iter_intlist_to_tbl)
It is nice to know there are many methods.
June 22, 2011 at 10:39 pm
Thank you for the article. It's building nicely on the methods of splitting strings by Jeff Moden and others. I am actually very curious about this sentence:
I've found that table variables used in a JOIN can be slow since the optimizer considers that there is only 1 row and the table has no statistics.
Can somebody else confirm or deny this? Recently I was tuning one very large stored procedure and one of the methods I tried for optimizing it was to replace table variables with temp tables (they are used in JOINs), however I haven't noticed appreciable difference in performance. This would, of course, be very specific to each individual scenario, so I am just posting this general question to see if anybody else has had any similar experiences.
June 23, 2011 at 11:30 am
Yes, the optimiser assumes only one row in a table variable.
February 28, 2013 at 11:44 pm
The most common reason I use much the same approach (even to the point of using Jeff's code too) is in passing multi-value parameters from SQL Server reporting services into stored procedures. I could I suppose include some preparatory code that creates a table variable, and then passes that as a parameter, but then I can't just use the reporting services "call a stored procedure" functionality, and it breaks the abstraction level of procedural logic too.
If you're writing inline queries in reporting services, you can do the "IN (@list)" thing... but I've found that embedding anything more than a simple query inside reporting services reports to be a poor practice. So that's not practical solution either.
A very real use case, and a thus a very real reason why the same "poor practice" (to paraphrase Joe Celko) gets reinvented constantly. While I appreciate Joe's dedication to SQL as a language, some of us actually have to use the thing, and purism aside, it becomes a case of "what are my business requirements; now how do I meet them?"
If Joe has an alternative that meets this use case, I'm all ears.
March 1, 2013 at 3:28 am
Elementary sub-query or EXISTS clause would
Radically improve performance.
If you like fancy coding you could pass XML as
parameter and use XQUERY.
March 1, 2013 at 4:43 am
If the JDBC driver supports tables as parameters to procedures, let me know 🙂
- heh, oracle supports it !!
After doing a search on the best way to pass a list of values (from java) to a stored proc, microsoft directed me to use XML instead of a delimited list.
This article gives a good run down of this approach...
https://www.simple-talk.com/blogs/2012/01/05/using-xml-to-pass-lists-as-parameters-in-sql-server/
March 1, 2013 at 6:19 am
One thing I found with TVPs is that they cannot be passed over a linked server :(. It was the only time I needed it and I too had to go with a split.
I have a split function that allows you to pass in # of columns too. So, '1,First Item,2,Second Item' becomes...
1 First Item
2 Second Item
I might try to apply some concepts here to enhance that function.
Nice work!
March 1, 2013 at 6:34 am
Brett Phipps (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".
SOP for Mr. Celko. I recommend using a DelimitedSplit routine for this type of thing on a regular basis, I just include it directly in the IN clause, not a separate CTE.
March 1, 2013 at 6:50 am
Nice article, thank you.
So we can make a dynamic in clause for a standard query. But we can't for the pivot clause.
Why Microsoft, why?
March 1, 2013 at 7:12 am
Thanks for article. Good reminder.
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply