关于sap:使用literal或者绑定变量执行SAP-HANA-SQL语句

There are two variants of SQL statement execution defined in SAP note 2000002 – FAQ: SAP HANA SQL Optimization. The difference is how the where condition is specified in SQL Statement.
Sometimes I prefer to call“Literals”as“Hard code”.

And for Bind variables, it means the where condition is unknown in compile time, but bound to a variable filled by application logic in the runtime. A typical example:

A caution in SAP note:
It can make a significant difference in terms of execution plan, performance and resource consumption if a SQL statement is executed with explicit literals or with bind variables. Therefore it is recommended that you analyze an expensive SQL statement that uses bind variables in the same way, i.e. also with bind variables.

See a real case to understand it.
I wrote the following report to search all Service Orders whose header“description”field contains specified key word.

REPORT zorder_query.
PARAMETERS: descr  TYPE crmd_orderadm_h-description OBLIGATORY DEFAULT 'Jerry',
            conta  TYPE char1 AS CHECKBOX DEFAULT abap_false,
            ttype  Type crmd_orderadm_h-process_type DEFAULT 'SRVO',
            ctype  type char1 AS CHECKBOX DEFAULT abap_false.

DATA: lt_selection_parameter TYPE genilt_selection_parameter_tab,
      ls_query_parameters    TYPE genilt_query_parameters,
      ls_selection_parameter TYPE genilt_selection_parameter.
DATA(lo_core) = cl_crm_bol_core=>get_instance( ).
lo_core->load_component_set('ONEORDER').

IF conta = abap_true.
  ls_selection_parameter = VALUE #( attr_name = 'DESCRIPTION' sign = 'I' option = 'CP'
 low = |*{descr}*| ).
ELSE.
  ls_selection_parameter = VALUE #( attr_name = 'DESCRIPTION' sign = 'I' option = 'EQ'
 low = descr ).
ENDIF.

APPEND ls_selection_parameter TO lt_selection_parameter.

IF ctype = abap_true.
   ls_selection_parameter = VALUE #(attr_name = 'PROCESS_TYPE' sign = 'I' option = 'EQ' low = ttype).
   APPEND ls_selection_parameter TO lt_selection_parameter.
ENDIF.
ls_query_parameters-max_hits = 100.
cl_crm_order_timer_home=>start( ).
TRY.
    DATA(lo_collection) = lo_core->dquery(
        iv_query_name               = 'BTQSrvOrd'
        it_selection_parameters            = lt_selection_parameter
        is_query_parameters                = ls_query_parameters ).
  CATCH cx_root INTO DATA(cx_root).
    WRITE:/ cx_root->get_text( ).
    RETURN.
ENDTRY.
cl_crm_order_timer_home=>stop('Search by Description').
WRITE:/ |Number of Service Orders found: {lo_collection->size() }| COLOR COL_NEGATIVE.

This report uses CRM BOL API to delegate the query to database layer, which finally executes the following SQL statement.

This execution belongs to“Bind variables”variant.

As a result, if we would like to paste the SQL statement from ABAP to HANA Studio, we should ensure we have pasted the“correct style”of SQL statement.

Find statement from Edit->Display Execution Plan->For Recorded Statement:

If you simply paste the following SQL statement into HANA Studio and execute it there, you actually failed to 100% simulate the ABAP SQL statement’s execution detail in HANA Studio.

The reason is: the SQL statement in ABAP has“Bind variables”variant, and the statement you paste below has“Literals”variant instead: pay attention to the area highlighted in blue below.

Correct approach
You can use this menu instead to get the appropriate SQL statement to be pasted:

Check the where condition which now has“Bind variables”variant. The variables here have been marked using“?”as placeholder.

Execute it in HANA Studio and there is a new tab“Prepared SQL”displayed. You should fill the actual value for each variable now. Choose“Add Parameter Values”from context menu:

and paste the following string into input field in popup dialog:
504,BUS2000116,BUS2000116,BUS2000140,BUS2000105,BUS2000137,BUS2000131,BUS2000146,BUS2000159,BUS2000153,BUS2000199,,Y,,%2017-12-21%,SRVO,100

Now each parameter has been automatically filled with corresponding value:

Execute SQL statement in HANA Studio. In this way, it has the same performance result as executed in SAPGUI.

要获取更多 Jerry 的原创文章，请关注公众号 ” 汪子熙 ”:

Wrong approach