Solved

reporting system takes longer time than sql server produces results.

Posted on 2014-09-18
7
282 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 46

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…

911 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

21 Experts available now in Live!

Get 1:1 Help Now