Menu
I'm trying to optimize a report that uses multiple stored procedures on the same table. Unfortunately, each procedure is reading millions of records and aggregating the results. It's a very intense read for a report, but each stored procedure is optimized to run pretty fast within SSMS.
I can run each stored procedure and get a result set within 10 to 20 seconds. When I put them all into one report within SSRS, the report times out.
There is a total of 4 parameters per stored procedure. All targeting the same table, just aggregating the data in different ways. Indexes on those tables are inline with the query. It's based on time, user and the one dimension I'm using to COUNT() both DISTINCT and NONDISTINCT.
I'm thinking the issue is the fact SSRS is running 4 procedures at the same time on the same table as opposed to one after the other. Is this true? If so, is there anyway to ensure SSRS does not run them in parallel?
My only option is to create summary table that is already preaggregated. Then just run the report off that table. Otherwise, I guess param sniffing is possible too.
252
You can include multiple SQL statements on the SQL query panel. The exceptions are CALL and CREATE PROCEDURE statements. These statements must be used alone in a query.
(Problem: SSRS can't handle multiple resultset from a stored procedure.) There are many other programming languages that can handle multiple resultsets from a stored procedure.
Simply put three queries one after the other in a . sql file, with semi-colons after each statement, then execute it as a script (either on a SQL*Plus prompt using @scriptname. sql or in TOAD/SQL Developer [or equivalent] using its script execution function).
To run a query with multiple statements, ensure that each statement is separated by a semicolon; then set the DSQEC_RUN_MQ global variable to 1 and run the query. When the variable is set to zero, all statements after the first semicolon are ignored.
By default, datasets in SSRS are executed in parallel.
If all of your datasets refer to the same datasource, then you can configure for serialized execution of the datasets on a single connection this way:
Once that checkbox is selected, datasets that use the same data source are no longer executed in parallel.
I hope that solves your problem.
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
