Reports taking too much time from application

Question

Post reply

Reports taking too much time from application

EasyBoy

SSCertifiable

Points: 5301
More actions
February 16, 2016 at 9:16 am

#316034

We have few (2-3) reports which are run by users through application everyday in the morning.
Through SQL profiler i found those queries (dynamic SQL), which are running through SQL server within 1-2 minutes but it's taking 1-2 hours for users through application.
What could be the possible reason? Can anyone please help?
This is really an ongoing issues from last few weeks and need urgent advice.
Thanks a lot.

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

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

When you run those queries from SSMS their resultsets must be cached already, so the queries aren't actually executed.

Try to run them at the end of the day, when SQL Server has already "forgotten" about those morning runs.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

EasyBoy (2/16/2016)
We have few (2-3) reports which are run by users through application everyday in the morning.
Through SQL profiler i found those queries (dynamic SQL), which are running through SQL server within 1-2 minutes but it's taking 1-2 hours for users through application.
What could be the possible reason? Can anyone please help?
This is really an ongoing issues from last few weeks and need urgent advice.
Thanks a lot.

Is the code for the reports generated by the application or stored in a stored procedure? (I suspect the former). If the dynamic SQL is NOT being executed by a call to sp_ExecuteSQL, that could be a part of the problem as well as what Sergiy mentioned. Overnight runs may be clearing the cache that the reporting code could have used.

Also, if your reports are taking 1-2 minutes in SSMS, it could be a simple case of poorly written code that can't take advantage of indexes, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61142 More actions · Answer 3

EasyBoy (2/16/2016)
We have few (2-3) reports which are run by users through application everyday in the morning.
Through SQL profiler i found those queries (dynamic SQL), which are running through SQL server within 1-2 minutes but it's taking 1-2 hours for users through application.
What could be the possible reason? Can anyone please help?
This is really an ongoing issues from last few weeks and need urgent advice.
Thanks a lot.

What are you using for your reporting? Is there different server for the DB and the Reports? Are there images or other objects that load on the reports? Roughly how many rows are these queries returning? Have you looked at the system resources on the reporting server when the reports run? Is the CPU going to 100%? Are you running out of memory on the App server? If so then it may have nothing to do with the query. This is the kind of information that can shed some light on your problem.

There are dozens of reasons that the query would be fast in SSMS and slow on the application server. Some examples include:

If your query is returning millions of rows... it could take a couple minutes in SSMS to return all the rows but hours in your reporting system. Assuming that you have your DB and Reporting on different servers (if not - that's a problem). With SQL Server Reporting Services (SSRS) - if your query is returning a lot of rows, say enough for 100's of SSRS "pages" worth and you have an large image(s) that loads on each page - that could slow you down; especially if the App server is wimpy.

That's my two cents based on the info you've provided.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001