Reporting performance looks good in execution log, but not in browser

Question

Hello.
In an effort to improve Reporting performance, I have optimized my reports with help I got from a previous post
However, even after improvements when I check the Execution Log, I still get slow performance in my reports.
In order to measure performance, I started a stopwatch at the same time I opened the report, and compared with the execution Log, here are my results:

ReportName	TimeDataRetrieval (ms)	TimeProcessing (ms)	TimeRendering (ms)	Total Time in Executionlog (s)	Time on Stopwatch(s)
Report1	10	28	171	0,209	10
Report2	10	49	394	0,453	12
Report3	1448	656	518	2,622	14
Report4	24	68	40	0,132	11

What this basically says is that even though Reports are optimized and have good response times in Execution log, they take around 10 seconds extra to load in Internet Explorer.

I made the exact same test on a Reporting Services instance with the following results:

ReportName	TimeDataRetrieval (ms)	TimeProcessing (ms)	TimeRendering (ms)	Total Time in Executionlog (s)	Time on Stopwatch(s)
Report1	109	10	139	0,258	1
Report2	143	13	425	0,581	6
Report3	123	9	167	0,299	6
Report4	307	12	15	0,334	2

Interestingly, the SSRS Reports also use the Azure SQL connection, which means it's quicker to display reports from Azure datasources in our local environment than in Azure Reporting.

I have ran these test 5 times in each environments with very similar results.

It would be great if anyone could explain this for me, and also give me some ideas on how to improve report performance further.

Thanks in advance,

Joel

Answer 1

Hi Joel S,

The total process time from clicking the report until showing up the report contain many aspect, it is relevant to your query, report's design, network status, distance from data center, distance from report server center, hardware, and so on. This is the reason why there is a little time on time on Stopwatch(s) column, but you need more time than this.

Current CTP version of SQL Azure reporting does not contain processing cache feature. However, product team are considering additional functionality for the next release as it is valuable for performance.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi,

Our experience is the same as Joel's!

Our application runs from North Central US data center and is sufficiently fast. Reporting CTP subscription is also in the same region.

I analyzed one of our reports today using execution log.

TimeDataRetrieval = 959 ms TimeProcessing = 482 ms TimeRendering = 629 ms

And the output is just one row! :)

Stopwatch shows anywhere between 12-15 seconds from clicking the VIEW button (in my app) to the time the rendering is completed.

I;m ignoring the distance to data center, since my application, SQL Azure database and reporting server are all EQUALLY far from me - I'm merely expecting a comparable performance to that of my application screens.

Besides the improvements that SQL Azure Reporting team could do, are there any tips for the developers out there? Any do's and dont's that can help avoid 10+ second overheads?

 

Regards,

kk