June 12, 2008 at 6:25 am
SSRS2005...
I have a large multi-value parameter list that I need to return to a SQL stored procedure. The list is too large to perform well with XML. I'm looking for ideas on how to accomplish this.
One idea I have is to write a function that you pass the multi-value list to. This function inserts each item in the list into a "ReportValues" table, along with a GUID (one GUID for all values). The function then returns the GUID, which is passed as a parameter to the SP. The sp retrieves the values from the table for that GUID. But is this even feasible?
Any and all ideas on how to accomplish this are gratefully appreciated!
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 12, 2008 at 7:02 am
That is one way. I remember seeing an article on this subject recently, but I could not find it.
Usnig a GUID and a table works - and pretty well. If you decide to use an actual table, clean-up can be a problem as well as lock escalation, so be sure to watch for these issues.
I have also had good luck using temp tables. If you open a connection to the database, create a temp table, populate it, and then execute your procedure or query all from that same connection without closing it, your temp table will stay in scope and you will never have conflicts between connections. I have found this to work really well.
June 12, 2008 at 7:27 am
Do you happen to have an example of doing this from a report?
Michael Earl (6/12/2008)
That is one way. I remember seeing an article on this subject recently, but I could not find it.Usnig a GUID and a table works - and pretty well. If you decide to use an actual table, clean-up can be a problem as well as lock escalation, so be sure to watch for these issues.
I have also had good luck using temp tables. If you open a connection to the database, create a temp table, populate it, and then execute your procedure or query all from that same connection without closing it, your temp table will stay in scope and you will never have conflicts between connections. I have found this to work really well.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 12, 2008 at 7:43 am
No, I don't - sorry. I assume you are using a custom front-end because Report Manager only gives you the option of having it build the "IN" clause for you. Report or app, the code would pretty much be the same.
June 17, 2008 at 3:09 pm
We use a function that splits the comma-delimited string sent by SSRS into a table, which is used in the stored proc. Search the forum for multi-value parameters and split functions - there are several variations.
This is what we use:
CREATE FUNCTION [dbo].[fn_ParseComma] (@STRING nvarchar(max))
RETURNS @Tmp TABLE
(
mystr nvarchar(150)
)
AS
BEGIN
DECLARE @Count int
SELECT @Count = 0
WHILE (SELECT CHARINDEX(',', @STRING)) > 0
BEGIN
INSERT @Tmp SELECT LTRIM(RTRIM(SUBSTRING(@STRING, 1, CHARINDEX(',', @STRING)-1)))
SELECT @STRING = LTRIM(RTRIM(SUBSTRING(@STRING, CHARINDEX(',', @STRING)+1, LEN(@STRING))))
END
INSERT @Tmp SELECT LTRIM(RTRIM(@STRING))
RETURN
END
- Marianne
June 18, 2008 at 9:54 am
I found the problem... it wasn't that it was too large of a "dataset" for sql to handle as XML.
In this report, we are literally building an XML string of 35,000 items. Once the string was built, SQL handled the XML just fine. In doing some performance testing, it was taking sql greater than 45 seconds to build an XML string, and less than 300 milli-seconds to return all of the items in it. When I changed the creation of the XML string to a select FOR XML statement, it would build the string in about 100 milli-seconds.
So, the performance problem was the building of the string itself. I was going through each element of the multi-value array one by one, adding to the string. Massive string manipulation has always been a performance issue with MS.
I changed the way the reports do this to use the Join() function, using a chr(255) as the delimiter. I then replace the chr(255) with the ending part of the XML string (to close off the first item) and the beginning part of the XML string (to start off the next item). Time went from a few minutes to less than 3 seconds. Acceptable.
All of the string splitting routines that I looked at suffered from the same issue with splitting apart that large of a string. They just took too long.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply