Report taking long time to execute

Question

Report taking long time to execute

srinadh.ramineni

Say Hey Kid

Points: 675
More actions
March 20, 2011 at 10:45 pm

#236523

I have a report with 2 parameters( from date & to date ) . When I am trying to see the report in the report viewer, it takes too much time to load it.
I tested the stored procedure manually by passing the 2 parameters. But the procedure is executing fine and it is giving the results in a fraction of seconds. But when i execute this report in the Reporting services, it is taking too much time and saying that requested time out.
If anyone faced this problem before, help me out from this problem.
Srinadh.Ramineni,
+91-9866596677

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

devaji123 Mr or Mrs. 500 Points: 502 More actions · Answer 1

Try this, go to run type inetmgr u can see IIS displayed and there select the project in the web sites and right click you can see properties in the properties there is a tab called performance in that unselect the first service or make it 0. by default it is 20 mins. Here wat it does is it will chk for all services and with in the default time if all the services are running then the report will get generated or you will get a message like timeout.

thanks

Vasu

grahamc SSCertifiable Points: 6762 More actions · Answer 2

What version of SSRS are you running? Are you returning any sort of TEXT or large BINARY fields?

Does the report only run the one query (procedure)?

vinayak.v SSCrazy Points: 2414 More actions · Answer 3

hi..

i'm also having the same problem ..

if the issue is solve please

tell me.. the solution..

i'm using RS 2008 enterprice edition..

help me...

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

A common problem with RS using stored procedures and parameters is the impact of Parameter Sniffing. So much so, that when I write a SP that I am going to use in RS I always write it with parameter sniffing in mind.

Alter your stored procedure. Declare variables local to the SP, in your case something like

DECLARE @Start DATETIME

DECLARE @End DATETIME

Then set the value for your local variables equal to the input variables

SET @Start = @ParameterInputStart

SET @End = @ParameterInputEnd

Then alter the rest of your stored procedure to use the local variables instead of the input variables.

WHERE TableDate BETWEEN @Start AND @End

Instead of

WHERE TableDate BETWEEN @ParameterInputStart AND @ParameterInputEnd

If that doesn't do it then you need to look for other issues. A good place to start is the view in your RS database: dboExecutionLog2. Or the ExecutionLogStorage table. Both have columns for TimeDataRetrieval, TimeProcessing, TimeRendering that can give you hints on where things are slowing down.

Hope this helps. Good luck.

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 5

Is it possible you've got the parameters set to TEXT or DATE, and then used a different parameter type (NVARCHAR OR DATETIME) when you're running it in SSMS? I've seen that affect execution times.

If not, then it's likely what Daniel said. I don't think I've run across the same parameter sniffing issue though, it's almost always a query optimisation issue like above with converting between types.