October 2, 2015 at 4:28 am
Because of a performance problem, somebody has given me a script which came from a SSRS report.
The code as supplied does not work when multivalued parameters are used.
Is there a workaround so that the code does work?
Ben
Testing/tuning/building in SSMS is far superiour than in SSRS. So that's why I like to use SSRS for building the code/script/sql-statement.
Offcourse parameters have to be set correctly. (That is no problem).
Splitting of the multivalued parameter is not a problem either.
October 2, 2015 at 5:26 am
how do you test in SSMS with multi value parameters, then?
it's going to come down to your implementation...can you paste the actual code?
the actual error message? "does not work" is just too vague for a meaningful solution to be offered.
typically, the problem is someone tries to do
AND somecolumn in (@MultivalueParameter) -- a varchar that happens to contain commas
wen they have to use a splitter function, like
AND somecolumn in (SELECT Item FROM dbo.DelimitedSplit8K(@MultivalueParameter,',') myfn)
but you said you don't have a problem splitting the multivalued parameter, so you'll have to provide specifics.
Lowell
October 2, 2015 at 11:53 am
Sorry, I think I was asking for something which is not obtainable.
And I wasn't clear enough in my question.
Because I want the code to cut and paste between the SSRS and SSMS, I do not want to change the code itself, only the preparation of the parameter.
And as far I understand it now that is not possible.
During the all the report server alters the code before it get's send to the server. So the actual code that is shown in the query builder of the report server does not run as it is, it needs some alteration. So took me some time to figure that out.
The actual report builder is reluctant to use a stored procedure for obtaining the information, and to figure out how to tune the query, I like to use SSMS, so I thought I just could cut and paste the code into some environments where the parameters where fixed and prepared for testing and then run the code as it came from SSRS. Not possible as I understand it know.
Took some time to figure that one out, so thanks for your time and attention,
Ben
October 3, 2015 at 5:34 pm
ben.brugman (10/2/2015)
Sorry, I think I was asking for something which is not obtainable.And I wasn't clear enough in my question.
Because I want the code to cut and paste between the SSRS and SSMS, I do not want to change the code itself, only the preparation of the parameter.
And as far I understand it now that is not possible.
During the all the report server alters the code before it get's send to the server. So the actual code that is shown in the query builder of the report server does not run as it is, it needs some alteration. So took me some time to figure that out.
The actual report builder is reluctant to use a stored procedure for obtaining the information, and to figure out how to tune the query, I like to use SSMS, so I thought I just could cut and paste the code into some environments where the parameters where fixed and prepared for testing and then run the code as it came from SSRS. Not possible as I understand it know.
Took some time to figure that one out, so thanks for your time and attention,
Ben
Not unattainable at all... The only thing SSRS does to the code is replace the @Parameter with the actual parameter value.
In the case of a multi-valued parameter, the value is a comma delimited aray of values.
So... If you have SSRS code that looks like this...
SELECT
p.BusinessEntityID,
p.PersonType,
p.NameStyle,
p.Title,
p.FirstName,
p.MiddleName,
p.LastName
FROM
Person.Person p
WHERE
p.BusinessEntityID IN (@CustomerID)
You just need to execute the code in SSMS like this...
SELECT
p.BusinessEntityID,
p.PersonType,
p.NameStyle,
p.Title,
p.FirstName,
p.MiddleName,
p.LastName
FROM
Person.Person p
WHERE
p.BusinessEntityID IN (1,2,3,4,5,6,7,8,9)
HTH,
Jason
edit... If you want to continue to use a parameter, do a Lowell suggested and use a splitter function...
DECLARE @CustomerID VARCHAR(8000) = '1,2,3,4,5,6,7,8,9';
SELECT
p.BusinessEntityID,
p.PersonType,
p.NameStyle,
p.Title,
p.FirstName,
p.MiddleName,
p.LastName
FROM
Person.Person p
WHERE
p.BusinessEntityID IN (SELECT ds.Item FROM dbo.DelimitedSplit8K(@DustomerID, ','))
October 4, 2015 at 6:32 am
ben.brugman (10/2/2015)
... not obtainable. ...... I do not want to change the code itself, ...
Jason A. Long (10/3/2015)
ben.brugman (10/2/2015)
Not unattainable at all... The only thing SSRS does to the code is replace the @Parameter with the actual parameter value.
This is a change to the code itself and then you can not cut and past the code between SSRS and SSMS and have the same functionality.
The key is that SSRS does the replacement before the server is called. My initial thought was that the code we see (saw and will see) in SSRS was ready runable code. My mistake.
Ben
October 6, 2015 at 2:32 am
Progress update,
Sorry that I can not post the actual code and or situation.
As allready described the code has to be altered before it can be executed.
There were some suggestions how to resolve the multi value parameters.
Cut and paste the value.
Or replace:
Lowell (10/2/2015)
AND somecolumn in (@MultivalueParameter) -- a varchar that happens to contain commas---->
AND somecolumn in (SELECT Item FROM dbo.DelimitedSplit8K(@MultivalueParameter,',') myfn)
The starting issue was:
Because of a performance problem
And the code change did give a bad performance. (Even in the end solution).
(Runtime for the above solution was 30 seconds to 1 minute).
So the code was changed to.
-- First a table is prepared. (Only in SSMS)
-- The string which was cut from SSRS contains '; ' as a sepparator.
SET @MultivalueParameter = REPLACE(@MultivalueParameter, '; ', ';')
Declare @MultivalueParametertable table (Item varchar(300))
INSERT INTO @MultivalueParametertable SELECT Item FROM dbo.DelimitedSplit8K(@MultivalueParameter,';')
--
-- In the actual statement Use:
--
AND somecolumn in (SELECT Item FROM @MultivalueParametertable)
-- For SSRS change the above line (back) to:
AND somecolumn in (@MultivalueParameter)
Runtime for this end solution was between 1 and 2 seconds.
If this time reproduces in SSRS, we have tackled our problem.
Doing this within SSRS would be an almost impossible task. Using SSMS as a testing platform speeds up the coding/testing and verifying the result.
Especially the verifycation of the results was important in this situation.
The end solution is again a single statement which can be used in SSRS. (With the replacement of the multi value parameter code).
In SSMS by dividing up the code in distinct steps we came to a far better grasp of the actual steps to be taken and could by far better verify the results. (The in between results and the end result). This was essential for tackling the problem.
Thanks all, for your advise and time and attention to this problem.
Ben
Issues solved during this problem:
-- Performance.
-- Correctness of the result.
-- Counting of NULL values.
-- Non uniqueness of names. (Same name different ID).
-- Within SSRS is was difficult to verify the results. Especially because there was no clear requirement what to doe with same name's.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply