Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Reporting Services Report taking LONG time to process


SQL Reporting Services Report taking LONG time to process

Author
Message
sql_noob
sql_noob
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Cheryl Hanlon
Cheryl Hanlon
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
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
Vaidy Mohan
Vaidy Mohan
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 14
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
aparna333
aparna333
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 65
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.
saxena200
saxena200
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 168
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
shruthy123
shruthy123
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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 timeCrying. Also I am creating reports connecting to Analysis Database and MDX scripts.

Thanks in advance for your replies.....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search