November 1, 2016 at 9:47 am
NOTE: I'm working on setting up a user in the system that has execute SP permissions so I can do a simple call from the SSRS report to a stored procedure, I know that should give me a big performance boost. My current focus is to streamline the query as much as possible.
I have a report that runs pretty well provided you limit the number of choices selected in the dropdowns. I've been asked to rework the report so we can run it a lot more wide open.
I've been doing SSRS development here and there for the last 5-10 years, but nothing hardcore. Build a solution, build a few reports to go along with them, tweak, repeat, etc.
I pulled the main query that generates the result set out of the dataset and I'm running it in SSMS. In order to be able to pass multi selection parameters to the query, I declared variables as tables and I'm populating them with data with insert @variable values ('abc'),('def'),etc and using
where column in (select * from @variable) instead of just where column in (@variable).
This seems really clunky, and truthfully, makes me doubt my ability to judge if it's running quicker. I usually copy and paste just the code from SSMS back into VS and remove all of the select * from and leave just the (@variable)
Is there a general best practice I should be using instead?
November 1, 2016 at 4:08 pm
Quick question. Is the report more than likely ran with all or some of the options? If all is a common practice I like to remove the filter to reduce the amount of work being done.
~Tamera Clark
Learning to be a SQL DBA one day at time.
November 2, 2016 at 7:08 am
I believe it's more a case of the users wanting to use the parameters they have so they do. What I'm trying to accomplish is to somehow mimic as closely as possible, what the user is doing in SSRS to be able to optimize where possible based on their choices, or look for errors.
Example - I took a parameter and changed it to multi select and started getting a truncation error. I know it was because of the parameter change, but I couldn't figure out why. Was hoping to reproduce it in SSMS, but wasn't able to. After some googling, I think it might have something to do with a max limit (there were a LOT of parameters because it is dependent on another dropdown, so the more you pick in ddl1, the more appear in ddl2).
The simple answer is probably to remove some of the parameters, but it was more of a general, "How do you test your reports" kind of query. 🙂
Thanks for the response!
November 2, 2016 at 10:20 am
Well changing parameters makes it hard for the query to get a good query plan. I try to write a good query that is efficient and then add all the parameters. If things are really bad you may need a DBA or DBA Dev to look at indexing and statistics, if those are wonky nothing you do is going to help.
Testing has multiple phases. You can execute the stored proc from SSMS, check the query plans and look to see what is happening. Then you still test again once you start triggering the proc from within a report in SSDT. And you would test it again once it is setting on the server.
When I have a lot of parameters I tend to use tables (this time temp) like you did, but I use function to split the values. I check to see if they have selected "all" (I have added that as an option.)
--IF SSRS PASSES IN ALL CHANGE TO NULL ELSE POPULATE TABLE WITH VALUES
IF @status = 'All'
BEGIN SET @status = NULL
END
ELSE
BEGIN
INSERT INTO #STATUS
SELECT Item
FROM dbo.DelimitedSplit8K(@STATUS,',')
END
Then my where clause
AND
((@STATUS IS NULL) OR ([Status] IN (SELECT ITEM FROM #STATUS)))
AND
This eliminates the filter if you are not using it. Does it save you a ton? Not sure it depends.
I am not sure what exactly you are working on but is it possible to use sub reports to bring more detail? This will offset some of the processing to another report.
Your truncation error might be due to a data type issue, max limit is something I haven't run across and I have messed with some SCSM reports with 26+ parameters. NUTS!
~Tamera Clark
Learning to be a SQL DBA one day at time.
November 2, 2016 at 11:09 am
I think that one of the things I need to focus on is the initial query to make sure it's like you said, write a good base query and then start putting in parameters.
There are only about 9 different parameters, but if you select all on one parameter, the 2nd and 3rd that are associated get absolutely huge. What we're finding is that no one really uses them so removing them all together is uncluttering things.
I don't worry much about the All choice, ours are just all of the choices are checked, but i'm copying that to my tricks folder in case I ever need it, so thanks! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply