I was recently building an application on Design Studio 1.3, when I faced quite a common problem – the application was taking too long to load. Performance is almost always affected by the time taken to access the datasource and the first step to optimizing the application is to know how fast or slow it currently is. Here are some simple ways to do just that.
The first thing I did to track query loading time was to enable Profiling. This gives a detailed analysis of how long each step takes to load. On execution, the two major steps are
- Time for data to be fetched from the remote database used (after disabling cache)
- Java rendering time. (This is when all the components get rendered)
Application runtime is the sum of the two.
To profile an application, first execute the application from Design Studio, on the BI platform or the NetWeaver platform. At the end of the URL, add ‘&PROFILING=X’ (note that it is case sensitive).
A dialog box opens up with the statistics. It shows total duration (in milliseconds) that the application took to load, Java rendering time and the Remote time.
Further details about the Java rendering time for each component can be found in the Rendering Statistics tab.
To test the correctness of profiling results, I built applications using BW on HANA and SAP HANA and profiled them. The data size used in both applications was around 30,000 rows.
Profiling on BW on HANA:
Profiling on HANA:
We can see that applications built on HANA database execute faster(remote loading of 0 ms), than those built on BW on HANA (7719 ms). This is as expected. BW on HANA is a faster alternative to BW on oracle or SQL database for the same known reasons – in memory and columnar storage.
Note that the ‘0 ms’ while executing a HANA application does not mean 0 exactly. The lowest time measured on Windows operating systems is 15.6 sec. So for any time period even slightly lower than that, profiling will show 0 ms.
Checking accuracy of Profiling
We’ve got our load times from profiling, but we need to make sure that the values displayed are accurate. There are ways to check the accuracy of the numbers by making use of performance analysis tools. We looked at RSRT for NetWeaver connections.
Comparing Profiling and RSRT
Before executing the DS application, cache has to be disabled. Otherwise profiling the application will show time taken to bring data from cache only and not the actual database.
From the similar values, we can see that Profiling gives a pretty accurate measure of application runtime and data fetch time.
While connecting to a universe, running BO auditing reports will give us time taken for the application to run. BO auditing reports can be accessed / built on top of the auditing universe. As soon as the application is run, details get logged in the auditing universe. The application must be run on the BI platform for it to be logged. Crystal / WebI reports can be built on top of this universe to get the required duration for which the application ran. More on this in upcoming blogs.