Solved

reporting system takes longer time than sql server produces results.

Posted on 2014-09-18
7
279 Views
Last Modified: 2014-10-07
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
Comment
Question by:25112
7 Comments
 
LVL 5

Author Comment

by:25112
ID: 40330242
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40332058
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40332161
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 16

Expert Comment

by:DcpKing
ID: 40332412
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40332476
@DcpKing. Oeps. Thanks for pointing that out. Unfortunately I can't delete my comments anymore.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 40332584
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
 
LVL 5

Author Comment

by:25112
ID: 40365880
your suggestions helped us to narrow down issues.. thanks.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now