Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems
We recently undertook a project for a customer where we reviewed the top X reports (in terms of usage as logged in SSRS) for performance optimization. Some of their heavy hitters were tying up a lot of DR resources, and slowing things down for everyone.
As part of that project, we looked at the usage of the ALL parameter in the SSRS reports. In this scenario, the first parameter in each SSRS pick-list was “ALL”, followed by the mnemonic from the dictionary. If multiple parameter items were picked, we pass them to the stored procedure as a pipe (|) delimited string.
That delimited string of items is then split apart and stored in a #temp table local to the stored procedure.
In the report SQL itself, these parameters were being processed like this:
Which, in most cases, was working fine. Sometimes, particularly where we had a lot of clauses like this in the report WHERE, it slowed things down.
After some experimentation, we found the following approach worked much better. Better execution plan, avoided Index scans… all good:
First, we still create a temp table (not a table variable!) to hold the mnemonics:
Load the temp table with the pieces of our passed-in string. Insert a tilde (~) if the parameter is ALL:
In the query use an inner join to select only matching records:
Note that this takes a bit more setup, however. But it is speedy!
Extra Credit
Read up no logical short-circuiting with the OR predicate.
And…
As ever, if you need help with Meditech DR optimization, reporting, extract, index creation or analysis please feel free to give your iatricSystems Sales Representative a call or email info@iatric.com to discuss how we can help support your team!
