report takes longer to run

Question

report takes longer to run

sqlfriend

SSC Guru

Points: 52794
More actions
May 1, 2012 at 9:14 am

#273443

We have a stored proc runs in less than a second on the database using SQL Management studio. But when running through the reporting services using the same parameter takes about 4-6 minutes to run. I don’t have the same issue with any of the other stored procs and reports. I know 4-6 minutes is not a big deal, but the user might think something is wrong. How can I troubleshoot this?

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

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

Lowell SSC Guru Points: 323738 More actions · Answer 1

sqlfriends (5/1/2012)
We have a stored proc runs in less than a second on the database using SQL Management studio. But when running through the reporting services using the same parameter takes about 4-6 minutes to run. I don’t have the same issue with any of the other stored procs and reports. I know 4-6 minutes is not a big deal, but the user might think something is wrong. How can I troubleshoot this?
EXEC [dbo].[WorkshopReport]
@WorkshopDate = NULL,
@SchoolId = NULL

sounds like one of two things...parameter sniffing, since you are defaulting the typical values for NULL:

you could use OPTIMIZE FOR UNKNOWN as one way to address it.

the other, is you probably created a catch all query, where you are testing for WHERE (WorkshopDate IS NULL OR WorkshopDate = @WorkshopDate) , which creates a poor plan due to the OR statements.

read Gails very popular article on it's ramifications:

sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

I found out the report runs fast on a 2005 reporting server, but when the same report is on a in-place upgrade 2008r2 server, it runs slow.

Lowell SSC Guru Points: 323738 More actions · Answer 3

you might remember there are a few other very similar posts which state that after an in place upgrade, rebuilding of statistics is an absolute requirement.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Thank you, I will give it a try, might need to upgrade the report in visual studio and redeploy it.