Passing in lists to SQL 2005 parameters, is there a better approach?
-
Hi all,
I was just looking for some alternative means of acheiving something;
Lets assume we have a customer table, each customer has a unique id and additional data.
We have procedure which select customer details using the customer id, which is fed in as a parameter, everything works perfectly.
Then a scenario arises where the procedure now needs to get multiple customer records, the parameter will no longer be a singular value, but instead a list of id values seperated by a character (lets say a comma).
Now in 2008 I could us a table parameter, however in SQL 2005 at present the best way I can think of doing this is to use a tabular function that seperates out the id (simple while loop), using the delimiter. Pretty simple and quick.
Does anyone have any better suggestions on how this could be acheived, as the alternative would be to make a large number of calls to the database (which may turn out to be a better option).
All comments welcome,
Thanks.
-
Jackal (6/4/2009)
Hi all,I was just looking for some alternative means of acheiving something;
Lets assume we have a customer table, each customer has a unique id and additional data.
We have procedure which select customer details using the customer id, which is fed in as a parameter, everything works perfectly.
Then a scenario arises where the procedure now needs to get multiple customer records, the parameter will no longer be a singular value, but instead a list of id values seperated by a character (lets say a comma).
Now in 2008 I could us a table parameter, however in SQL 2005 at present the best way I can think of doing this is to use a tabular function that seperates out the id (simple while loop), using the delimiter. Pretty simple and quick.
Does anyone have any better suggestions on how this could be acheived, as the alternative would be to make a large number of calls to the database (which may turn out to be a better option).
All comments welcome,
Thanks.
Not necessarily better than a delimted list, but you could use XML.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Typically a string split using a numbers/Tally table or CTE will outperform a while loop. For a small list a while loop is probably okay. Here is the numbers/tally solution using a CTE.
DECLARE @list VARCHAR(100)SET @list = '1,2,3,4'
;WITH cteNumbers AS
(
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
cteNumbers
WHERE
N + 1 <= 100
)
SELECT
Substring(',' + @list + ',', N+1, CHARINDEX(',', ',' + @list + ',', N + 1) - N - 1)
FROM
cteNumbers
WHERE
N < LEN(',' + @list + ',') AND
Substring(',' + @list + ',', N, 1) = ',';
For a full explanation see this article[/url].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
For a small list, use the Numbers / Tally table method.
For a large list, use XML. See my article[/url] that covers this. It even includes a comparison to the Tally table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
[font="Verdana"]I'd go for passing in the list as XML, even for the small list. The reason is that it's not difficult to get an object in .NET to write itself into a string as XML (which can then be passed to the database).
Also, you can write gateway procedures that use typed XML so they can verify that the parameter is in the correct format (and I believe there are some performance increases for typed XML, but I can't verify that.) You have to coordinate the schema for the XML between the application and the database, so that may be more work than is useful.
Once you get this in place, you can use it as a generic approach. So you can pass a data-set to a stored procedure as XML to save it, or call a stored procedure and have it return XML, or pass sets of data as XML between your application and SQL Server broker, or throw the XML from the application into MSMQ and have the database pull it off or... :w00t:
[/font]
-
Hi all,
Many thanks for the responses, certainly somethings to think about and try out.
-
Just one quick question,
I've created a function for which I can pass in XML and then the element name that I wish to extract, this should allow me to us the same function for multiple scenario's. However I cant get it dynamically use my parameter (@searchelement), im using the code below, any ideas on what I'm doing wrong.
SELECT i.item.value('var[@name = sql:variable("@searchelement")] [1]','nvarchar(15)')
FROM @XMLInput.nodes('/root/data') AS i(item)
Thanks,
-
Can ignore that now, manged to resolve it using;
i.item.value('(*[local-name()=sql:variable("@searchelement")])[1]','nvarchar(15)')
Thanks.
-
WayneS (6/4/2009)
For a small list, use the Numbers / Tally table method.For a large list, use XML. See my article[/url] that covers this. It even includes a comparison to the Tally table.
Hi Wayne
I disagree with that. The decision to use a Tally solution or XML does not depend on the count of items but on the length of the items within the text to be split.
I just tried with a simple 10,000 items INT list and the tally solution works about 5% faster on my system. A CLR split function works 50% faster than both.
For this test Tally requires about 100,000 numbers:
[font="Courier New"]--DELETE FROM Tally
--INSERT INTO Tally
-- SELECT TOP(100000)
-- ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- FROM master.sys.all_columns c1
-- CROSS JOIN master.sys.all_columns c2
--ALTER INDEX ALL ON Tally REBUILD
SET NOCOUNT ON
DECLARE @Csv VARCHAR(MAX)
DECLARE @Xml XML
SELECT @Csv = ',' +
(
SELECT TOP(10000)
CONVERT(VARCHAR(10), N) + ','
FROM Tally
FOR XML PATH('')
) + ','
SELECT @Xml =
(
SELECT TOP(10000)
N
FROM Tally
FOR XML RAW, ROOT('root')
)
DECLARE @Result TABLE (Id INT)
DELETE FROM @Result
PRINT '---============= Tally ========================='
SET STATISTICS TIME ON
INSERT INTO @Result
SELECT
SUBSTRING(@Csv, N + 1, CHARINDEX(',', @Csv, N + 1) - N - 1)
FROM Tally
WHERE
N < LEN(@Csv)
AND SUBSTRING(@Csv, N, 1) = ','
SET STATISTICS TIME OFF
PRINT ''
DELETE FROM @Result
PRINT '---============= Xml ========================='
SET STATISTICS TIME ON
INSERT INTO @Result
SELECT
T.C.value('.', 'int')
FROM @Xml.nodes('root/row') T(C)
SET STATISTICS TIME OFF
PRINT ''
DELETE FROM @Result
PRINT '---============= CLR ========================='
SET STATISTICS TIME ON
INSERT INTO @Result
SELECT
Item
FROM dbo.ufn_clr_SplitString(@Csv, ',')
SET STATISTICS TIME OFF[/font]
Greets
Flo
-
Bruce W Cassidy (6/4/2009)
[font="Verdana"]I'd go for passing in the list as XML, even for the small list. The reason is that it's not difficult to get an object in .NET to write itself into a string as XML (which can then be passed to the database).Also, you can write gateway procedures that use typed XML so they can verify that the parameter is in the correct format (and I believe there are some performance increases for typed XML, but I can't verify that.) You have to coordinate the schema for the XML between the application and the database, so that may be more work than is useful.
I would actually recommend something just slightly different. In .NET, use the JOIN function to create a delimited list (by default, it uses a comma, but can be changed to anything). This separates the application from the database just a little bit further... otherwise both have to be on the same XML specification. By passing a delimited list, you can use the method specified in my article[/url] to then, on the SQL side, convert it to XML and use it as a table.
For a large list, this will minimize the network traffic between the application and the server to just the data needed.
Edit: corrected typo
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Jackal (6/5/2009)
Can ignore that now, manged to resolve it using;i.item.value('(*[local-name()=sql:variable("@searchelement")])[1]','nvarchar(15)')
Thanks.
Now that promises to be a useful trick that I haven't seen before. Thanks for sharing it!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Happy to help.
I've created 2 functions, one thats using a loop to break up a string and then one using XML (thanks for the article - very useful).
On small data sets they both operate at around the same cost, however I'm thinking the XML will certainly be more useful for working with the developers, and for larger datasets.
So many thanks for the assistance.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply