Friday 9 February 2018

Execute SQL Statement in HANA Studio using Literals or Bind variables

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”.

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

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:
SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

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.

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

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.

Wrong approach


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

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

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.

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Correct approach


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

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

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

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

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:

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

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

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Now each parameter has been automatically filled with corresponding value:

SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Execute SQL statement in HANA Studio. In this way, it has the same performance result as executed in SAPGUI.
SAP HANA Studio, SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

No comments:

Post a Comment