✒️ABAP La performance en ABAP
ABAP La performance en ABAP
Comparación de Performance de SQL
Hola Compañeros:
Os dejo aquí el código fuente de un análisis de performance de SQL configurable mediante el uso de buenas y malas practicas en la sentencia SELECT. El tiempo y los registros recuperados aparecen en la barra de estado con un mensaje de STATUS. La consulta es un cuádruple JOIN con selección. Yo no percibo grandes aumentos de rencimiento, tan solo un 15%-20% entre el peor y el mejor caso.
- *&---------------------------------------------------------------------*
- *& Report ZGZ57B_SQL_PERFORMANCE
- *&
- *&---------------------------------------------------------------------*
- *&
- *&
- *&---------------------------------------------------------------------*
- REPORT zgz57b_sql_performance.
- *---------------------------------------------------------------------*
- * Tablas de la Base de Datos SAP
- *---------------------------------------------------------------------*
- TABLES:
- kna1, "Maestro Clientes
- t005t, "Nombre de los Paises
- vbak, "Cabeceras de Pedido
- vbap. "Lineas de Pedido
- *---------------------------------------------------------------------*
- * Tipos para las Tablas de los Informes
- *---------------------------------------------------------------------*
- TYPES:
- BEGIN OF ty_detalle,
- land1 LIKE kna1-land1, "Codigo Pais
- landx LIKE t005t-landx, "Nombre Pais
- ort01 LIKE kna1-ort01 , "Ciudad
- kunnr LIKE kna1-kunnr , "Codigo Cliente
- name1 LIKE kna1-name1 , "Nombre Cliente
- vbeln LIKE vbak-vbeln, "Num.Pedido
- audat LIKE vbak-audat, "Fecha Pedido
- netwr LIKE vbak-netwr, "Valor Pedido
- posnr LIKE vbap-posnr, "Linea de Pedido
- matnr LIKE vbap-matnr, "Cod.Articulo Linea
- arktx LIKE vbap-arktx, "Desc.Articulo Linea
- kbmeng LIKE vbap-kbmeng, "Cant.Articulo Linea
- netpr LIKE vbap-netpr, "Precio Articulo Linea
- END OF ty_detalle.
- *---------------------------------------------------------------------*
- * Tablas Internas y Registros (Workareas)
- *---------------------------------------------------------------------*
- DATA:
- ti_detalle TYPE STANDARD TABLE OF ty_detalle,
- wa_detalle LIKE LINE OF ti_detalle,
- wa_detalle_aux LIKE wa_detalle, " Auxiliar para corte Detalle
- so_ty_city(30) TYPE c,
- gv_t_ini TYPE i,
- gv_t_fin TYPE i,
- gv_records TYPE i,
- gv_mess(60) TYPE c,
- gv_time(12) TYPE c,
- gv_items(10) TYPE c,
- v_selected LIKE sy-tabix.
- *---------------------------------------------------------------------*
- * Variables de Acumulados de Corte
- *---------------------------------------------------------------------*
- DATA:
- gv_lineas_fra TYPE i, "Lineas en Pedido
- gv_bruto_fra TYPE p DECIMALS 2, "Bruto Pedido (cant x precio)
- gv_fras_cliente TYPE i, "Pedidos por Cliente
- gv_total_cliente TYPE p DECIMALS 2, "Importe Pedidos Cliente
- gv_fras_city TYPE i, "Pedidos por Ciudad
- gv_cli_city TYPE i, "Clientes por Ciudad
- gv_total_city TYPE p DECIMALS 2, "Importe Pedidos Ciudad
- gv_fras_pais TYPE i, "Pedidos por Pais
- gv_cli_pais TYPE i, "Clientes por Pais
- gv_total_pais TYPE p DECIMALS 2, "Importe Pedidos Pais
- gv_fras_total TYPE i, "Pedidos en Total
- gv_cli_total TYPE i, "Clientes en Total
- gv_gran_total TYPE p DECIMALS 2. "Importe Total Pedidos
- *---------------------------------------------------------------------*
- * Pantalla de selección
- *---------------------------------------------------------------------*
- SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
- SELECT-OPTIONS so_pais FOR t005t-land1. "Seleccion Pais
- SELECT-OPTIONS so_cli FOR kna1-kunnr. "Seleccion Cod.Cliente
- SELECT-OPTIONS so_fecha FOR sy-datum. "Seleccion Fecha Pedido
- SELECT-OPTIONS so_city FOR (so_ty_city)
- DEFAULT 'A' TO 'ZZ'. "Seleccion Ciudad
- SELECTION-SCREEN END OF BLOCK b1.
- SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.
- PARAMETERS:
- p_all RADIOBUTTON GROUP grp1, "SELECT *
- p_field RADIOBUTTON GROUP grp1, "SELECT fields
- p_table RADIOBUTTON GROUP grp2, "... INTO TABLE
- p_loop RADIOBUTTON GROUP grp2. "SELECT ... ENDSELECT
- *SELECTION-SCREEN PUSHBUTTON 'Actualizar Opciones'.
- SELECTION-SCREEN END OF BLOCK b2.
- SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE text-003.
- PARAMETERS:
- p_jchl RADIOBUTTON GROUP grp3, "Pais->Cliente->Cabecera->Linea
- p_jlhc RADIOBUTTON GROUP grp3. "Linea->Cabecera->Cliente->Pais
- SELECTION-SCREEN END OF BLOCK b3.
- CLEAR:
- gv_lineas_fra,
- gv_bruto_fra,
- gv_fras_cliente,
- gv_total_cliente,
- gv_fras_city,
- gv_cli_city,
- gv_total_city,
- gv_fras_pais,
- gv_cli_pais,
- gv_total_pais,
- gv_fras_total,
- gv_cli_total,
- gv_t_ini,
- gv_t_fin,
- gv_t_ini,
- gv_t_fin,
- gv_records,
- gv_mess,
- gv_time,
- gv_gran_total.
- *-------------------------------------------------*
- START-OF-SELECTION.
- *-------------------------------------------------*
- * GET RUN TIME FIELD gv_t_ini.
- IF p_jlhc IS NOT INITIAL.
- IF p_table IS NOT INITIAL.
- IF p_all IS NOT INITIAL.
- GET RUN TIME FIELD gv_t_ini.
- SELECT *
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF TABLE ti_detalle
- FROM vbap AS l
- INNER JOIN vbak AS f
- ON l~vbeln = f~vbeln
- INNER JOIN kna1 AS c
- ON c~kunnr = f~kunnr
- INNER JOIN t005t AS p
- ON c~land1 = p~land1
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- ENDIF.
- IF p_field IS NOT INITIAL.
- "Consulta de Lineas de Pedido
- "Lineas JOIN Cabeceras JOIN Clientes JOIN Paises
- GET RUN TIME FIELD gv_t_ini.
- SELECT c~land1 p~landx c~ort01 c~kunnr c~name1 f~vbeln
- f~audat f~netwr l~posnr l~matnr l~arktx l~kbmeng l~netpr
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF TABLE ti_detalle
- FROM vbap AS l
- INNER JOIN vbak AS f
- ON l~vbeln = f~vbeln
- INNER JOIN kna1 AS c
- ON c~kunnr = f~kunnr
- INNER JOIN t005t AS p
- ON c~land1 = p~land1
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- ENDIF.
- ENDIF.
- IF p_loop IS NOT INITIAL.
- IF p_all IS NOT INITIAL.
- GET RUN TIME FIELD gv_t_ini.
- SELECT *
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF wa_detalle
- FROM vbap AS l
- INNER JOIN vbak AS f
- ON l~vbeln = f~vbeln
- INNER JOIN kna1 AS c
- ON c~kunnr = f~kunnr
- INNER JOIN t005t AS p
- ON c~land1 = p~land1
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- APPEND wa_detalle TO ti_detalle.
- ENDSELECT.
- ENDIF.
- IF p_field IS NOT INITIAL.
- GET RUN TIME FIELD gv_t_ini.
- SELECT c~land1 p~landx c~ort01 c~kunnr c~name1 f~vbeln
- f~audat f~netwr l~posnr l~matnr l~arktx l~kbmeng l~netpr
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF wa_detalle
- FROM vbap AS l
- INNER JOIN vbak AS f
- ON l~vbeln = f~vbeln
- INNER JOIN kna1 AS c
- ON c~kunnr = f~kunnr
- INNER JOIN t005t AS p
- ON c~land1 = p~land1
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- APPEND wa_detalle TO ti_detalle.
- ENDSELECT.
- ENDIF.
- ENDIF.
- ENDIF.
- IF p_jchl IS NOT INITIAL.
- IF p_table IS NOT INITIAL.
- IF p_all IS NOT INITIAL.
- GET RUN TIME FIELD gv_t_ini.
- SELECT *
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF TABLE ti_detalle
- FROM t005t AS p
- INNER JOIN kna1 AS c
- ON c~land1 = p~land1
- INNER JOIN vbak AS f
- ON c~kunnr = f~kunnr
- INNER JOIN vbap AS l
- ON l~vbeln = f~vbeln
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- ENDIF.
- IF p_field IS NOT INITIAL.
- "Consulta de Lineas de Pedido
- "Lineas JOIN Cabeceras JOIN Clientes JOIN Paises
- GET RUN TIME FIELD gv_t_ini.
- SELECT c~land1 p~landx c~ort01 c~kunnr c~name1 f~vbeln
- f~audat f~netwr l~posnr l~matnr l~arktx l~kbmeng l~netpr
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF TABLE ti_detalle
- FROM t005t AS p
- INNER JOIN kna1 AS c
- ON c~land1 = p~land1
- INNER JOIN vbak AS f
- ON c~kunnr = f~kunnr
- INNER JOIN vbap AS l
- ON l~vbeln = f~vbeln
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- ENDIF.
- ENDIF.
- IF p_loop IS NOT INITIAL.
- IF p_all IS NOT INITIAL.
- GET RUN TIME FIELD gv_t_ini.
- SELECT *
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF wa_detalle
- FROM t005t AS p
- INNER JOIN kna1 AS c
- ON c~land1 = p~land1
- INNER JOIN vbak AS f
- ON c~kunnr = f~kunnr
- INNER JOIN vbap AS l
- ON l~vbeln = f~vbeln
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- APPEND wa_detalle TO ti_detalle.
- ENDSELECT.
- ENDIF.
- IF p_field IS NOT INITIAL.
- GET RUN TIME FIELD gv_t_ini.
- SELECT c~land1 p~landx c~ort01 c~kunnr c~name1 f~vbeln
- f~audat f~netwr l~posnr l~matnr l~arktx l~kbmeng l~netpr
- BYPASSING BUFFER
- INTO CORRESPONDING FIELDS OF wa_detalle
- FROM t005t AS p
- INNER JOIN kna1 AS c
- ON c~land1 = p~land1
- INNER JOIN vbak AS f
- ON c~kunnr = f~kunnr
- INNER JOIN vbap AS l
- ON l~vbeln = f~vbeln
- WHERE p~spras = sy-langu "Nombre de Pais en ES
- AND c~land1 IN so_pais
- AND c~ort01 IN so_city
- AND c~kunnr IN so_cli
- AND f~audat IN so_fecha.
- APPEND wa_detalle TO ti_detalle.
- ENDSELECT.
- ENDIF.
- ENDIF.
- ENDIF.
- DESCRIBE TABLE ti_detalle LINES gv_records.
- * Tiempo de ejecucion
- GET RUN TIME FIELD gv_t_fin.
- gv_t_ini = gv_t_fin - gv_t_ini.
- WRITE gv_t_ini TO gv_time.
- WRITE gv_records TO gv_items.
- CONCATENATE gv_items 'resultados en' gv_time 'microsegundos.'
- INTO gv_mess
- SEPARATED BY ' '.
- MESSAGE gv_mess TYPE 'S'.
- *-------------------------------------------------*
- END-OF-SELECTION.
- *-------------------------------------------------*
Sobre el autor
Publicación académica de Carlos Piles Rosell, en su ámbito de estudios para la Carrera Consultor ABAP.
SAP Senior
Carlos Piles Rosell
Profesión: Analista de Sistemas y Programador - España - Legajo: GZ57B
✒️Autor de: 24 Publicaciones Académicas
🎓Egresado de los módulos:
Disponibilidad Laboral: PartTime
