Performance difference filtering selection screen vs within a query

[former_member565982]

Hi,

We are building a BW Query where we want to filter on a lot of values in the selection screen (~50.000) as the end user. The execution of the Query takes 180 s. However if we instead goes directly into the Query (no filters) it takes maybe 1-2 s. Then we apply the filter within the Query the time to filter is only around 30 s i.e. 1/6 of the time!?

When I look in the profiling log it seems to be "BICS_PROV_VAR_SET_VARIABLES" that takes a very long time. Why is this? Is there anything we can do about it since the user experince would be much better if we could use the selection screen instead of filters within the Query.

We are on BW 7.5 SP18.

Best regards

Marcus

[john_lang]

Hi Marcus, Good question.

The 1-2 seconds performance (with no individual selection filters specified) is the in-direct proof that the data model itself appears to be ok, saying this with a high level of confidence. 🙂

Often, the heart and core of these types of issues tend to be with the, a SELECT * WHERE {for ~50,000 individual values} as the bottleneck down at the database layer (Cube, Aggregate, DSO, ADSO, etc, where the data actually lives) ... but you've indicated that the time bottleneck is in the execution preparation before the database gets hit. Hummm, ok, then there are three things to consider:

(1) If its the SAP standard code for variable processing then the answer is "not much can be done".

(2) If you trace through the query execution, can you confirm for sure that is is the sap standard code (option 1 above), or is it perhaps the customer exit processing of reporting variables in the 'ZXRSRU01' include. It might be the case that the custom exit code added to that system has LOOP or READ statements on the I_T_VAR_RANGE internal table that are sub-optimal. A senior ABAPer within your team (even an ECC ABAPer) should be able to assess the not-so-obvious optimisations available for iterating internal tables way faster.

(3) Consider changing the definition of the reporting variable to allow intervals, and only use inclusive logic (no NOT logic, no < > comparison logic) at the time you execute the query.

(3.1) Use intervals, not individual values ... provided you have the luxury of the individual values being able to fit within a range of values and still honour the business intention of that range being fully inclusive; including the values not in your original single value list but are ok (from a business point of view) to still be specified within the values of the interval range.

(3.2) Only use inclusive (equal to) logic; avoid the 'not' logical operators, if possible.

(3.3) Only use specific value (equal or between); try to avoid the < > (less than or greater than) logical operators, if possible.

I realise these suggestions doe not magically address the direct issue, but it will help remove known performance bottleneck situations that the question is focused on.

Hope this helps.

Kind regards, John.

[former_member565982]

Hi John,

thank you for answering!

I have gone through your bullet points and none of them should be a problem in this case. I did the test with a "clean" variable i.e. pasting in the individual values in the selection screen vs pasting in the values after executing the query without filtering in the selection screen. The only thing I can think of is if there is some kind difference in the application layer when filtering is the selection screen vs filtering within the query.

Best regards

Marcus

[john_lang]

Hi Marcus,

Appreciate the thoroughness and feedback to allow this to be a discussion.

I agree that it has been narrowed down to ”is if there is some kind difference in the application layer when filtering is the selection screen vs filtering within the query”. It would be safe to conclude that the DataModel & the Query potions of the dataset journey (from the database up to query result table), really is ok.

If the custom variable for reporting user exit code has been eliminated as a performance bottleneck (which it seems have been eliminated) then the only short-term tweak I know of is to shift from using single values (~50,000 unique variable entry values) over to use intervals, which might get this down to < ~5,000 unique variable entry values. However, this is an overhead that might not be practical (pre-processing to convert unique values into intervals). Then there might be gaps (real business values) contained within those interval definitions which in turn makes the query result set to invalid (maybe?).

Ok, so, having typed this out and re-reading it … intervals are a technical-only solution to the real bottleneck problem, but it is a double-edged sword because of how the intervals might be put together. There could be no ‘value compression’ benefit if they are too unique or if the intervals cover more range (with gaps) then it could introduce business integrity problems in the result table. Ugh, ugly, so intervals might not be a practical solution after all.

In essence, I agree that there is probably not much more that can be done to speed this up; not without investing a load of developer time in the pre-processing (with no guarantee the unique values will compress into intervals enough to make a difference).

Ok, shifting focus … depending upon how the unique list of ~50,000 values are identified and populated into the query variable selection filter screen; given that you had identified that the query ran faster when filters were applied after execution, is there an opportunity to explore either of these capabilities:

1. Add a ‘report-to-report (RRI)’ interface to the query, to allow a different query to do the work of collecting the unique list of values and then the target query (the one you use now) gets this list from that other new query. In an earlier version of BW, this was a fast way to avoid the manual intervention for big custom lists of master data values (removing human error from the equation).
   
2. If RRI is not an option, is there a business rule available that defines a programmatic (ABAP) way to get the list of unique values from elsewhere in the BW DataModel? This would allow you to explore a custom variable for reporting that populates the ~50,000 values in I-STEP2. Which should be after where we believe the current processing bottleneck might be (we suspect that it is currently in the standard code after I_STEP1 and how the BW system populates the values form the user selection screen going ‘into I_STEP2’.

Both of these options require BW developer exploration and possibly a bit of trial/error/learning, which you might not have time for at the moment.

As general advice from a public forum, it would seem there is no quick fix for the scenario/dilemma you are facing but definitely a few more creative off-shoots that only a BW Developer (Query builder + ABAP, but not a reporting user) could explore (time and effort permitting).

Hope this helps.

Kind regards, John.