CODEWITHSUNDEEP
sql-serverperformancereporting-servicesssrs-2008
user1390916
user1390916

Reputation: 31

SSRS sql query runs slow

I have a long time issue keep popping up every time. I create ssrs report with some select query. when i try to run the report it takes around 20sec to render. i've checked the sql profiler and indeed the query run more than 20 sec. when i copy the query to the management studio, it runs in 0 sec.

as written in earlier posts i've tried the walk around of declaring parameters in the query and setting their value with the ssrs params. sometime it works, currently it doesn't...

any other walk around?

Upvotes: 3

Views: 557

Answers (2)

cakiran
cakiran

Reputation: 341

Make the sql statement into a stored procedure and use the WITH RECOMPILE option in the sp.

E.g.

CREATE PROCEDURE dbo.spname @ParamName varchar(30)

**WITH RECOMPILE**

AS

This will help counteract the "parameter sniffing" during the procedure execution and help improve performance.

Upvotes: 0

Diego
Diego

Reputation: 36146

Configure your report to run from the cache.

Caching is a copy of the last executed report. It is not a persisted copy, it has a lifetime (like caching for 30 minutes). It is stored on the temp database. You can have only one "instance" per report (if you have parameters, you will have one per combination of parameter)

you can do that on the execution tab of the report on report manager

Upvotes: 2

Related Questions