Open SQL

Open SQL is how ABAP reaches the database. It hides differences between database products, so the same code runs on Oracle, SQL Server or HANA.

What it provides

Database independence, so SQL dialects do not matter
Automatic client handling in the WHERE clause
Automatic use of table buffers
Table-level authorisation checks

SELECT

Reading data

SELECT SINGLE matnr, mtart, meins
  FROM mara INTO @DATA(ls_mara)
  WHERE matnr = @lv_matnr.

SELECT matnr, mtart, meins
  FROM mara INTO TABLE @DATA(lt_mara)
  WHERE mtart = 'FERT'
  ORDER BY matnr.

Five rules for performance

Rule	Detail
Select only the fields needed	Name fields rather than using SELECT *
Select only the rows needed	Filter in the WHERE clause, not afterwards
Never SELECT inside a loop	One database round trip per row
Respect indexes	WHERE fields should match the index from its first field
Aggregate in the database	Use SUM and GROUP BY rather than looping

The anti-pattern and its fix

" Bad: a SELECT per row
LOOP AT lt_items INTO ls_item.
  SELECT SINGLE maktx FROM makt INTO ls_item-maktx
    WHERE matnr = ls_item-matnr AND spras = sy-langu.
ENDLOOP.

" Better: fetch once, then join in memory
IF lt_items IS NOT INITIAL.
  SELECT matnr, maktx FROM makt
    INTO TABLE @DATA(lt_makt)
    FOR ALL ENTRIES IN @lt_items
    WHERE matnr = @lt_items-matnr AND spras = @sy-langu.

  SORT lt_makt BY matnr.

  LOOP AT lt_items ASSIGNING FIELD-SYMBOL(<fs>).
    READ TABLE lt_makt INTO DATA(ls_makt)
         WITH KEY matnr = <fs>-matnr BINARY SEARCH.
    IF sy-subrc = 0.
      <fs>-maktx = ls_makt-maktx.
    ENDIF.
  ENDLOOP.
ENDIF.

FOR ALL ENTRIES

Things to know

Behaviour	Detail
An empty driver table selects everything	The WHERE condition disappears
Duplicates are removed	Row counts may not match expectations
It is split into several statements	Many round trips for large driver tables

Joins

An inner join

SELECT a~matnr, a~mtart, b~maktx
  FROM mara AS a
  INNER JOIN makt AS b ON a~matnr = b~matnr
  INTO TABLE @DATA(lt_result)
  WHERE a~mtart = 'FERT' AND b~spras = @sy-langu.

Writing data

Code pushdown and CDS views

With HANA, pushing work into the database matters. Rather than pulling large volumes to the application server and processing there, joins and aggregations run in HANA and only the result is returned.

A CDS view

@AbapCatalog.sqlViewName: 'ZVMATSUM'
@AccessControl.authorizationCheck: #CHECK
define view Z_Material_Summary as
  select from mara as m
    inner join makt as t on m.matnr = t.matnr
{
  key m.matnr as Material,
      m.mtart as MaterialType,
      t.maktx as Description
}
where t.spras = $session.system_language

📖 Unfamiliar term? Look it up in the SAP glossary.