Skip to Content
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.

Wrong approach

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.
To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Michelle Crapo

    So which is better literals or blind variables? Or does it depend, run the program? I haven’t used blind variables, but I have used literals.

    Thank you,

    Michelle

    (0) 
    1. Denys van Kempen

      Hi Michelle,

      It depends.

      [apologies in case I’m explaining known concepts]

      A query needs to be parsed by the database engine (is the query valid, do the objects exist, privileges, what’s the best execution plan, etc.)  and this takes CPU time. To improve response time,  the plan of this query is stored in the SQL Plan Cache for when this exact same query is executed again. Bind variables allow for reuse of the query plan for queries that are almost the same but not exactly the same: only the variable differs for which a placeholder is now used.

      However, imagine a query with a condition on a column of a very large table with few distinct values, say 99% of the time, the value is 1 and in 1%, the value = 0. In case of the 0, using this column early in the execution plan with a column lookup would be a good choice as we can filter out a lot of rows. For the 1 case, we would probably try to filter first on other conditions or do a full table scan. Very different execution plans, very different response times if the wrong path is chosen, yet, with bind variables, the same plan will be stored in the cache.

      In other words, in general, using bind variables is a good approach as the database does not have to parse almost identical queries each and every time. However, there might be cases where it is not such a good idea and maybe even a very bad idea.

      Check, check and double check.

      In your view, would this be a good topic for blog/video tutorial series?

      Regards,

      Denys / SAP HANA Academy

      Subscribe to our YouTube Channel
      Join us on LinkedIn
      Follow us on Twitter
      Github code samples
      Facebook

       

      (2) 
      1. Michelle Crapo

        You weren’t repeating know concepts to me.  Ah yes, it depends.

        Thank you for the great explanation on how to start deciding which to use.

        Michelle

        (1) 

Leave a Reply