Where the bottle neck in a report

Question

Where the bottle neck in a report

MotivateMan1394

Hall of Fame

Points: 3876
More actions
August 2, 2015 at 2:27 am

#299376

Hi guys
We have a web application.
We deployed All Servers (Data server + Web server) On One data center.
Our Web app have some Main and Heavy reports . (WareHouse Reports)
-------------------------------------------------------------------------------------
User complains :
- In some reports with some conditions (that result is about 70.000 Or 40.000 records with about 20 Or 40 MB Size of report), we get Error.
I Know the Stored Procedure behind The reports. I want to check the Cause of Error :
1- I run sp on my client . >>>>> It was very heavy and at the end gets error. (After 2 or 4 minutes)
2- I change sp which the result doesnot send to client and save to a table (Select * Int temp_table from ....) >>>> Result All of them run Ok .
Rep1 in 18 secs and Rep2 in 48 Secs Runs without error.
Can I result that : The network between data center and client have problems.
But some one said to me that Your result is not true. Because in first step you run sp on your client But in App runs at web server that is near the data server.
Then .... I dont Know how to check this sp? (Check client report on a remote App such as citrix is ok or not?)
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

It seems incredulous to me that anyone would request a report that contains 40.000 or 70.000 rows to begin with. No one will be reading that. The only use that I can see is that someone wants that much data to dump into a spreadsheet or some-such to do additional analysis.

My recommendation would be to find out what that analysis is and aggregate the report(s) a whole lot more before sending them to the client. Considering the run time of the current reports, some thoughtful performance tuning would seem to be in order.

To summarize, the problem is likely not in the pipeline nor at the client. The problem is in the code that aggregates the data for the report.

--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)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

Jeff Moden (8/2/2015)
It seems incredulous to me that anyone would request a report that contains 40.000 or 70.000 rows to begin with. No one will be reading that. The only use that I can see is that someone wants that much data to dump into a spreadsheet or some-such to do additional analysis.

+1

have seen it a thousand times....then once provided as requested, spent inordinate time delivering what "they" really wanted but were unable to specify until report was produced.

repeat, rinse repeat

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 3

These are interested Ideas about Diagnosis Real request. I didn't see in this aspect.

But in this time, This is my main question :

In a web application, A client (web client) recieve Data (Report Result) OR Web server is main data receiver ?

And If the client is main data receiver ,

Is a tool such as citrix Useful to reduce data transportation ?

Prakash-205770 Ten Centuries Points: 1079 More actions · Answer 4

Please make sure if you have configured memory settings for SSRS in RSReportServer.config file.

For example(sample values only):

Refer to https://msdn.microsoft.com/en-us/library/ms159206.aspx

Kindest Regards,

R

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 5

MotivateMan1394

Hall of Fame

Points: 3876

August 4, 2015 at 2:11 am

#1817024

we dont have SSRS.

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 6

Jeff Moden (8/2/2015)
It seems incredulous to me that anyone would request a report that contains 40.000 or 70.000 rows to begin with. No one will be reading that. The only use that I can see is that someone wants that much data to dump into a spreadsheet or some-such to do additional analysis.

Not the only use Jeff,

when printed out it can make a passable door stop.

I'd place the problem in the spec of the original request.