Check for problematic SELECT * using Code Inspector

[Former Member]

Hi,

I'm currently reading the course HA400 "ABAP Programming for SAP HANA" and I have an inclear issue concerning the problematic SELECT * statement using the Code Inspector.

In the Exam Sample for "SAP Certified Development Specialist - ABAP for SAP HANA (Edition 2015)"

Link: https://training.sap.com/shop/certification/e_hanaaw151-sap-certified-development-specialist---abap-...

we got the following Question:

1. You create a check variant in the Code Inspector which includes the check 'Search problematic Select *
statements'. SAP HANA is used as the database
In the check parameters, which value would you use for the percentage of fields that are effectively used?


Please choose the correct answer.:

a) A lower value than on a traditional, row-store only based database  
b) The same value as on a traditional, row-store only based database
c) A value that you calculate based on the number of tables
d) A higher value than on a traditional, row-store only based database

Actually, only answer a) or d) came in question.

I have tipped for answer a) which was the correct answer.

However, I have get a problem of understanding this:

In the HA400 course documentation of the year 2015, it saids on page 75/76 the following:

The check for problematic SELECT * statements in particular has several check parameters. This check searches for SELECT * FROM dbtab statements where less than a specified percentage of the fields are effectively used. The default value for the percentage is 20 percent. If less than 20 percent of the fields are used it is more efficient to select only the used fields. The value if this parameter highly depends on the database used. When on a database with column store, e.g. SAP HANA, this value should be set to a lower value than on a traditional, row store only database.

So, if the "percentage of fields" is set to 20% it has to be set for SAP HANA to a lower value e.g. 10% (Answer 'a)' )

This is actually not correct to my point of view, we need to have a higher value (Answer 'b)' ), e.g. 40% or 60%. As all problematic SELECT * statements which have between 1% and 40% of fields not used will be listed.

When we go to the HA400 course document of the year 2013, it was said at page 57 that the value should be HIGHER than on a traditional row store database:

The check for problematic SELECT * statements in particular has several check
parameters. This check searches for SELECT * FROM dbtab statements where
less than a specified percentage of the fields are effectively used. The default value
for the percentage is 20 percent. If less than 20 percent of the fields are used it is
more efficient to select only the used fields.
The value if this parameter highly depends on the database used. When on a
database with column store, e.g. SAP HANA, this value should be set to a higher
value than on a traditional, row store only database.

I'm quick not well understanding the context of phrases.

For ME, the percent need to increase from 20% to 40% or upper, as we don't like to transport all columns with HANA. This operation is quite costly for column-Store. That is why the search for SELECT * need to have all entries which have a lower percentage of use of the fields than e.g. 40%.

For Me the answer is d)

d) A higher value than on a traditional, row-store only based database
HA400 Course 2013, Page 57

But SAP said a)

a) A lower value than on a traditional, row-store only based database  
HA400 Course 2015, Page 76

I really don't know why they changed it.

Can, anyone with some English Skills find a solution for this ambiguity.

Thanks for your repond,

Kais

[alwinkoshy2000]

Both a and d could be correct depending on the way it is tackled.

Lets assume we have columns named column_1, column_2....to column_100.

If the number of columns used are column_1 to column_21 and if the % set is 20 then there will be no error thrown when select * is used. This way by not throwing an error we are calling remaining 79 columns which are not required causing performance issue in HANA database. This problem will further increased if we reduced the percentage from 20.

On the other hand let say we increase the percentage to 30 or 40. If we use only 20 columns but call 30 columns in the select query there will be no error thrown in this case as well causing performance issue due to these additional 10 columns being called.

Assuming that you never use select * as an experienced developer reducing the % from 20 will have more benefit because statistically we are likely to use  less than 20% columns for any select queries and having a error message for using 21% columns will not cause dramatic performance issues even if you ignore to correct it.

[raphaelbertani]

To resolve that and skip this validation, use dinamic select ('*') instead select *