Parallel query execution and performance measuring

[d_chang]

One cool feature that came with Design Studio 1.5 is the ability to execute queries in parallel. In this blog I want to show you how you can use this feature to speed up your dashboards. I will also show you how you can measure the performance of your dashboard, so that you can tackle potential bottlenecks.  In some cases it might not always be useful to use this feature. I noticed that when you use parallel query execution you definitely win time when fetching data from BW, but you lose some time to render the data on the screen. By measuring what happens you can make a good trade off.

Let’s start with a simple query that I will use throughout this example.

Let’s take a look at the query execution time in BW. Go to transaction RSRT and hit the Execute + Debug button.

Select Display Statistics Data from the list to perform a simple Analysis

Hit Continue. The query will be executed. If there is a prompt available in the query you will be asked t o fill in a value. The results of your query will now be shown on the screen.

Now, hit the back button. An overview of query statistics will be shown. From the screenshot here you can see that in total it takes about 3.3 seconds to execute the query. This is including the 2.5 seconds of waiting time (the time i needed to fill in the variable values). So, basically the query needs about 0.8 seconds to execute.

Now in Design Studio I use the same query to check if the performance is more or less the same. In Design Studio I used a crosstab to display the query on the screen. Execute the application. In the URL of the application, add the following command at the end of the URL: &PROFILING=X

When you do this a statistics window will pop up

From this information you can see that the total runtime is about 1.7 seconds, divided into 2 parts:

1. 1. Java: 0.987 seconds. This is the time the application needs to render the data on screen
2. 2. Remote: 0.7 seconds: This is the time the application needs to fetch the data

So at this point we can see that the query execution is just about what we expect compared to BW.

Let’s change some settings in Design Studio.

First I edit the initial view of the data source and add a different member to the rows section of the data source. Let’s see how this will affect the performance.

In the screenshot below you can see that the remote part is slightly more than before. However, you should keep in mind that the values that are shown here a probably really fast because of the cache used.

Let’s add another data source to the application and execute the application. You can see from the screenshot below that the application takes a little bit longer to execute.

Although the performance decrease is not spectacular in this case, I still want to show you how to set up the parallel query execution function.

In the properties of each data source specify a unique Processing Group, in this case I typed in G1 and G2

Also in the application properties change the Merge Prompt option from true to false

Now, execute the application again and measure the performance.  You can now see that the query execution time decreases.

However, look at the Java time. This value increases and it looks like the overall time the application needs is more than it was without parallel query execution (in this case)!

In this example the total execution time is less when we do not use the parallel query execution. For queries that need more processing time, you would definitely win when you use parallel query execution. It might also be interesting to see how well your scripts or filters perform. You can measure each navigation step in your application using the build-in statistics in Design Studio.