##### Child pages
• Sample Query 2
Go to start of banner

# Sample Query 2

In case the query result set is big, the usage of the feature 'zero suppression' may lead to a high memory consumption:

## Query without Zero Suppression

The query has 2 structures with, one has 13 and the other one has 10 structure elements (130 cells, in other words, this corrsponds to a single structure with 130 structure elements). There are 2 infoobjects in the drilldown. The query result has 3465410 rows:

If you divide 3465410 by 13 (number of structure elements of the structure drilled down on the Y axis, Jan2011 - Jan2012) you get 266570. This means we have 266570 different value combinations in the drilldown, in this simple example (e.g. no yellow result lines and no OLAP features like exception aggregation) this number corresponds approximately to the number of data records transferred to the OLAP Engine (see DBTRANS below). If we multiply the number of rows with the number of columns(10) we get FE_CELLS = 34,654,100. This number is much greater than OLAP_FE_TRANS ! The reason is that we have many cells with NULL values (empty cells, not booked, such cells are not handled by the OLAP engine and hence not included in OLAP_FE_TRANS ) !

In order to use the estimation formula we need the query runtime statistics and the two parameters VF_G and C_CP given in the 'Technical Information':

Now we apply the estimation formula

OLAP Memory Consumption = (VF_G*24 + C_SP*8 + CHARS_DRILL*7) * DBTRANS   +   96 * OLAP_FE_TRANS = (4*24 + 120*8 + 2*7)*284.873 + 96*14416545 = 0.3 GB + 1.384GB

So in total the result of our estimation is 1.684 GB. Let's compare this value with the what the transaction ST02 displays:

This workprocess allocates about 2.4 GB memory. The difference to our OLAP estimation is about 0.7 GB and there are two main points which contribute to this 0.7 GB:

• the texts of charcteristic values and attributes
• and the NULL values (see above)

If you check the memory consumption in the debugger right after the processing block 'Data Transfer' (see How to check current Memory Consumption) you get the following values displayed:

So the memory consumption of the OLAP engine is actually 1.9 GB which is already closer to our calculated value. As mentioned above, the difference is mainly caused by

• the texts of charcteristic values and attributes
• and the NULL values (see above)

## Query with Zero Suppression

If the query is executed with the feature 'zero suppression' active (applied on the rows) the memroy cosnumption will increase considerable:

Please note that the number of rows has gone down to 2,394,622. The total memory consumption increased to about 4GB which is almost the double of what the query needed without zero suppression:

Hence please only use this feature if really necessary for big queries!

• No labels