Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Fast query runs slow in SSRS

Tags:

performance

reporting-services

report

reportingservices-2005

I have an SSRS report that calls out to a stored procedure. If I run the stored procedure directly from a query window, it will return in under 2 seconds. However, the same query run from an 2005 SSRS report takes up to 5 minutes to complete. This is not just happening on the first run, it happens every time. Additionally, I don't see this same problem in other environments.

Any ideas on why the SSRS report would run so slow in this particular environment?

like image 774
user275554 Avatar asked Feb 17 '10 19:02

user275554

People also ask

Why is SSRS so slow?

If your stored procedure uses linked servers or openquery, they may run quickly by themselves but take a long time to render in SSRS. Some general suggestions: Retrieve the data directly from the server where the data is stored by using a different data source instead of using the linked server to retrieve the data.

How do I reduce report rendering time in SSRS?

If a single report is processing slowly, tune report dataset queries if the report must run on demand. You might also consider using shared datasets that you can cache, caching the report, or running the report as a snapshot.

Why does SSRS take so long to load?

It's "normal" that SSRS takes more time to load the first time you access it after a long time of inactivity. The issue is caused by the way how SSRS works and SSRS regularly restarts application domain after a specific time period.

What is parameter sniffing in SSRS?

SQL Server creates an optimal plan for a stored procedure by using the parameters that are passed the first time to the stored procedure is executed is called Parameter Sniffing.

1 Answers

Thanks for the suggestions provided here. We have found a solution and it did turn out to be related to the parameters. SQL Server was producing a convoluted execution plan when executed from the SSRS report due to 'parameter sniffing'. The workaround was to declare variables inside of the stored procedure and assign the incoming parameters to the variables. Then the query used the variables rather than the parameters. This caused the query to perform consistently whether called from SQL Server Manager or through the SSRS report.

like image 197
user275554 Avatar answered Sep 21 '22 21:09

user275554
Sign in to Comment

Related questions

Performance Benchmarking of Contains, Exists and Any
nth fibonacci number in sublinear time
Why does my Intel Skylake / Kaby Lake CPU incur a mysterious factor 3 slowdown in a simple hash table implementation?
Why is adding an OnClickListener inside onBindViewHolder of a RecyclerView.Adapter considered bad practice?
What is microbenchmarking?
Android ListView Refresh Single Row
Performance of qsort vs std::sort?
Performance considerations for keySet() and entrySet() of Map
Fastest way to get a positive modulo in C/C++
How do I find out what is hammering my SQL Server?
Visual Studio Build Very Slow
Efficient way to do batch INSERTS with JDBC
Why is the loop instruction slow? Couldn't Intel have implemented it efficiently?
What is the fastest Java collection with the basic functionality of a Queue?
Is it more efficient to perform a range check by casting to uint instead of checking for negative values?
Perm space vs Heap space
Performance with infinite scroll or a lot of dom elements?
Is accessing data in the heap faster than from the stack?
Why is lodash.each faster than native forEach?
Matrix multiplication: Small difference in matrix size, large difference in timings

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!