Exceptionally slow report execution

[KCcasey]

Hi,

I'm having huge problems with the run-time of a stored procedure from RS Designer (and from the server when the report is uploaded).

I have a SP with about 6 parameters. When executed from QA the SP takes less than a second to return a result-set of about 1500 rows. When running the SP from RS (Designer > Data tab) using query parameters the excution time is around 3 mins !!!

Looking at the TimeDataRetrieval field in ExecutionLog table in the ReportServer DB, the SP is the problem (consuming about 99% of the time involved in the report generation). I've tried adding indexes, I'm not using report filters....

Are there any known problems with RS in executing SPs with multiple params or JOINs ...??

Help!

Thanks in advance,

Casey.

 

[ookete]

Hmm, odd. I have never heard of a problem with sproc executions like you have described. For me, it has always been the same in Query analyzer or Report Designer. Are you certain that nothing is different in the parameters you are passing (ie. correct data types, same date ranges, etc)?

Good luck on this one, and let us know what you find.

 

[ookete]

You might want to look at this post from a few weeks ago: thread1462-1094879. It has some interesting stuff in it about RS Execution speeds.

 

[Catadmin]

Question. Are you just running Exec MyProc <varlist> in the data set or are you actually running the code from MyProc in the dataset? (i.e. Select * from MyTable...)

The later will have a time lag. The former, like Ookete, I haven't heard of problems on. My thought on the former would be there might be a network issue between your two servers (IIS & SQL). Ask your network admin to help you run packet tests between the two machines. They might not be in the same physical location or on the same subnet.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???