Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL Reporting Services Report taking LONG time to process Expand / Collapse
Author
Message
Posted Tuesday, April 13, 2010 1:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 1:20 AM
Points: 1, Visits: 14
has there any resolution on this issue? We are having the same issues with rendering reports from a sql querie on IIS. Managers are complaining that it slow as a turtle gettng just a month's worth of data.
Post #902176
Posted Wednesday, April 14, 2010 2:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 6:29 AM
Points: 29, Visits: 79
Hi,

I have actually been dealing with the same issue over the past 24 hours and this is how i resolved it.

Firstly I added some proper indexes on the database, I was still having speed issues. Then i disabled parameter sniffing on the query.

Basically ssrs sniffs the parameter coming into a report and can sometimes produce a convulted execution plan based on those parameters. To disable parameter sniffing you should create a local variable within your sproc or sql statement and assign the parameter to that instead.

I.E If you passed a variable from your report called @customerId and your query looked like this

select * from customer where id = @customerId

Change it to

declare @localCustomerId varchar(10)
set @localCustomerId = @customerId

select * from customer where id = @localCustomerId

This will produce a much more efficient execution plan if parameter sniffing is the problem.

Also if you are using a sproc try setting nocount on

Hope this helps !

Cheryl
Post #902935
Posted Thursday, December 08, 2011 12:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 08, 2011 4:00 AM
Points: 2, Visits: 2
Hi Cheryl Hanlon,

It's quite terrific point that you have pointed out. It literally saved me from a disaster today. I was facing the same issue and by replacing parameters with local variables, it rendered my data within 5 seconds. Awesome.

Now I have to go back to all my SPs to get this implemented.

Thanks so much for this point.

Vaidy Mohan
Post #1218410
Posted Wednesday, January 11, 2012 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 2:49 AM
Points: 1, Visits: 49
Hi,

How did this issue get rresolved?
I am facing a similar problem now but could not find any solution.

I've a stored procedure which is taking 22 sec when i execute it from SSMS.The number of rows being returned are 2962.

But when i execute the Report which is uses the same stored proc from report server, is taking 3 min to process.


I have checked the report execution log and it shows more time for data retrieval but not rendering or processing.

My report does not have any aggregations or groups .there are no filters and yes I have handled parameter sniffing but that did not help much.
I am displaying the data returned by data set in a simple table(tablix) control without any grouping or sorting.
The time taken to load the report is increasing with the increase in number of rows being returned by the stored procedure.
Also,I dO not have any paging in my report as per the requirement.all the records are displayed in single page. The interactive page size is set to 0
What could be the possible reason for the report server to take long time to display the report.
Post #1234149
Posted Friday, March 01, 2013 11:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 12:31 AM
Points: 30, Visits: 96
Thats a great solution but i have a problem, my parameters have multiselect option setting local parameters is giving me error, can any one help... please

Query -

DECLARE @LOCAL_VAR_ORGN VARCHAR(MAX)
SET @LOCAL_VAR_ORGN in (@Organisation)
DECLARE @LOCAL_VAR_BL VARCHAR(MAX)
SET @LOCAL_VAR_BL in @BusinessLine
DECLARE @LOCAL_VAR_SE VARCHAR(MAX)
SET @LOCAL_VAR_SE in @SalesExecutive

select vd.Month,
vd.month_No,
vd.Year,
vd.Week,
vd.Date,
vd.Organisation,
vd.[Business Line],
vd.[Sales Executive],

count(distinct vd.CSTMR_KEY)[Total no of Lines],
(
case when SUM(vd.[Total Amount Due])>0
then COUNT(distinct vd.CSTMR_KEY) else 0 end
)[Total No Of Unpaid Lines],

SUM(vd.[Total Amount Due])[Total Amount Due]
from IV_DBT_RPT_FR_ORGNSTN VD

where (vd.Date>=@FromDate and vd.Date<=@ToDate)
AND VD.Organisation IN (@LOCAL_VAR_ORGN)
AND VD.[Business Line] IN (@LOCAL_VAR_BL)
AND VD.[Sales Executive] IN (@LOCAL_VAR_SE)

group by vd.AR_BHVR_KEY,
vd.[Business Line],
vd.C,
vd.CNTRCT_DD,
vd.CSTMR_KEY,
VD.Year,
vd.Date,
vd.month_No,
vd.Month,
VD.Week,
vd.Organisation,
vd.[Sales Executive]

--order by VD.Date
Post #1425672
Posted Saturday, June 01, 2013 3:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 24, 2014 3:53 AM
Points: 4, Visits: 22
Hi All,
I also have similar kind of issue. I can view the report in BIDS with in few seconds. but when the same report is viewed in ReportManager its taking very long time. Also I am creating reports connecting to Analysis Database and MDX scripts.

Thanks in advance for your replies.....
Post #1458927
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse