Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

reporting system takes longer time than sql server produces results.

what are the factors that can lead for a reporting server to take too much time to deliver the reports, after the sql server has finished its query.
0
25112
Asked:
25112
2 Solutions
 
25112Author Commented:
SQL server profiler suggests the call finished in 22 seconds (profiler status of Batch Start and BatchComplete). But the reporting system displays the report after 11 minutes.

1)SQL Server can track any more than the  last 'BatchCompleted'. can it?
2)what are the factors that can make ibmcognos to take that time?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You may run with network problems.
You report may have some event that is trying to process the information after the request from the database.
You may have another application running in the reporting server, like an antivirus for example.

I'm only guessing since there's very few information for we can have some clues in what's happening with your system.
0
 
Nico BontenbalCommented:
There are a number of logs for SSRS. Some of those can be used for performance diagnosis and optimization:
http://msdn.microsoft.com/en-us/library/ms157403.aspx

But getting the data is only one part of the report creation of course. SSRS has to render this data as well. Grouping, sorting, formatting etc.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
DcpKingCommented:
Nicobo: the author specifies IBM/Cognos as the reporting engine, not SSRS.

25112: Questions to ask include:
   -   How much data is being thrown at the reporting engine?
   -   Where is the report being rendered?
   -   What else is the reporting server doing at the time?

hth

Mike
0
 
Nico BontenbalCommented:
@DcpKing. Oeps. Thanks for pointing that out. Unfortunately I can't delete my comments anymore.
0
 
mlmccCommented:
The comment is still relevant since most reporting systems do log activity.

Where is the report being rendered?
If it is on the server the wall clock time for running doesn't equate to the cpu time for rendering.  At what point in the process does the SQL run?
Say you submit at 0800 and it completes at 0811.
If the SQL runs at 0809 then the report isn't taking much time to render.
However if the SQL runs at 0801 then perhaps there are issues with the report.

What is the filter on the data for the report?
Not all filtering can be done on the server.  If the filter is built in a way that it is using Cognos functions then all data from the database may be returned and the significant filtering is done in the report without the speed of indexes on the data,

mlmcc
0
 
25112Author Commented:
your suggestions helped us to narrow down issues.. thanks.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now