Time for a change - Parallel processing of multiple statements (flows) within a single unx query

[Former Member]

Overview

The objective behind this post is to try and bring some attention to what I consider a serious product shortfall, namely the sequential processing of multiple SQL statements within a single universe query.

The below idea touches on this briefly, it also references a secondary issue which is the sequential processing of multiple dataproviders in a single document - another topic which needs addressing.

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=1DA84A30-1E5A-43FA-95C5-857A8B99D197&idea_id=3DE05DC2-...

The Issue

As mentioned this post focuses on the execution behaviour of a single unx query that contains multiple statements (flows) and the sequential processing thereof. These queries are generally invoked when the following options have been set on the universe:

• Multiple statements for each context
• Multiple statements for each Measure

Steps to reproduce:

• Create a universe containing multiple contexts
• Enable the option 'Multiple statements for each context'
• Select a measure from each context, this will produce 2 statements (observed when selecting view script)
• Profile/monitor database activity
• Execute the query
• Note the sequential execution of each statement

This behaviour is documented in SAP Note: 1258337. This document is slightly dated but from what I've seen the behaviour hasn't changed.

Findings

The below components all use a java library to execute and retrieve the results of a .unx query from the database.

• Crystal Reports Processing Server (Crystal Reports for Enterprise)
• Dashboard Processing Server
• Information Design Tool
• Dashboard Designer
• Crystal Reports for Enterprise Client

Generally speaking the guts of the code that performs the query execution is held in a class called com.businessobjects.dsl.services.dataprovider.impl.QuerySpecDataProvider which can be found in the jar files:

• dsl_engine.jar
• com.businessobjects.dsl.services.impl.jar

Changing the below two methods, in the class com.businessobjects.dsl.services.dataprovider.impl.QuerySpecDataProvider, to include a simple asynchronous call when running the recursive calls to getResultNode(<?>) would bring about significant performance improvements when executing queries that contain multiple statements:

• getResultNode(<?>)
• getMergedResults(<?>)

By making the changes above you should in theory see vast improvements in overall query performance  when:

• You have a large number of statements in a batch
• And/Or statements within the batch take a long time to run

It is important to note that the final output will always take as long as the longest running statement in the batch as all results need to be retrieved before they can be passed up the stack.

Please note that the workflow is slightly different for components that access the Webi Processing Server, namely:

• Webi Reports
• PowerBI
• BIWS

The Webi processing server utilises a different code base, I’m struggling to get some readable traces together for this application component but my guess is changing the underlying code, compiled in QT.dll, in line with the above recommended change would have a similar positive affect.

Conclusion

The above findings are the result of testing against a single source relational unx universe, behaviour may differ when using multisource universes/olap connectivity - both of which I haven't got round to testing yet.

With that in mind by no means am I proposing that the above small change to the referenced class is a complete solution but one would hope that given the available manpower at SAP, coupled with the performance improvements this change can bring to the product suite, a full solution could be implemented by SAP in a relatively short space of time - given enough push by us the community.

I've listed several ideas below that detail where sequential processing takes place. Please up-vote them if you want to see the current implementation improved and ultimately reap the performance gains these changes will bring.

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=1DA84A30-1E5A-43FA-95C5-857A8B99D197&idea_id=3DE05DC2-...

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=BB5523E4-062F-4420-B35F-0B1F0D4769A9&idea_id=19CD336C-...

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=A5E8DEA8-D886-4250-BA2B-039F7D32FFC0&idea_id=BE83AB34-...

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=1DA84A30-1E5A-43FA-95C5-857A8B99D197&idea_id=24E4A4A2-...

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=1DA84A30-1E5A-43FA-95C5-857A8B99D197&idea_id=54372DF0-...

For those JAVA devs out there, it is of course possible to decompile the com.businessobjects.dsl.services.dataprovider.impl.QuerySpecDataProvider class and implement this change in order to reap the performance benefits, however there is a high likelihood that merely the act of decompiling the code would be a copyright infringement so this is by no means endorsed or recommended by me.

Let the crusade begin......hopefully.

Cheers

James