asked
SSRS Report Parameter Performance
Hi All, We are having an SSRS report which has around 14 multi value parameters which get the distinct value of the fields to display. And each filter is cascaded based on the parent selection. The problem is each filter is taking 4-5 mins to display the respective fields. when I try to run the same query in the SQL Server, the execution is very fast comparatively. I am using simple select statements with couple of joins. Help us with your suggestions
I've had fun with this too! SSRS tends to re-run the queries behind the parameters quite a lot. My impression is that every time you change a parameter value it re-runs all the others but I haven't found confirmation of that. There are advanced options for when each parameter should refresh. The default is Let SQL Decide and neither of the alternatives - Always Refresh or Never Refresh - sounds like the solution if your parameters are linked and they are slow. I concentrated on making sure those queries were nice and fast by refactoring the queries and indexing the tables so they were as quick as possible. There's one giant table where only a few of the records have ever been used so I copy the used rows into another table during my ETL and use that for the parameter list. Hope that's given you a few ideas.
18 People are following this question.
