Publiée le septembre 18, 2011 12:20
| nombre de lecture : 2503
Lire aussi : http://www.b-integration.net/Default.aspx?tabid=433&articleType=ArticleView&articleId=374&language=fr-FR
Introduction
Après mes deux précédents articles consacrés à SQL, je vous propose de revenir sur les meilleures pratiques SQL en les comparant en détail. Pour cela je me suis inspiré du cours SAP. Le but de cet article est d'objectiver la performance de différentes stratégies de développement en ABAP SQL.
Le scénario de base s'appuie sur l'application SFLIGHT (si vous n'avez pas de donnée dedans lancer le rapport SAPBC_DATA_GENERATOR. Il s'agit en fait de réaliser deux SELECT imbriqués d'abord dans la table SFLIGHT puis dans SBOOK et faire une somme.
Stratégie n°1 : Couples table interne + work area imbriqués.
Le code
REPORT ZBENCHSQL1.
DATA: sflight_itab TYPE sflight OCCURS 100 WITH HEADER LINE,
sflight_wa TYPE sflight.
DATA: sbook_itab TYPE sbook OCCURS 100 WITH HEADER LINE,
sbook_wa TYPE sbook.
DATA: sum TYPE sbook-loccuram VALUE 0.
DATA: t1 TYPE i, t2 TYPE i, delta(16) TYPE p.
GET RUN TIME FIELD t1.
SELECT * INTO CORRESPONDING FIELDS OF TABLE sflight_itab
FROM sflight
WHERE carrid = 'LH'
AND seatsmax_f = sflight~seatsocc_f.
LOOP AT sflight_itab INTO sflight_wa.
SELECT * INTO CORRESPONDING FIELDS OF TABLE sbook_itab
FROM sbook
WHERE carrid = sflight_wa-carrid
AND connid = sflight_wa-connid
AND fldate = sflight_wa-fldate
AND loccurkey = 'EUR'.
LOOP AT sbook_itab INTO sbook_wa.
sum = sum + sbook_wa-loccuram.
ENDLOOP.
ENDLOOP.
GET RUN TIME FIELD t2.
delta = t2 - t1.
SKIP.
WRITE: / 'SQL 1 : ' COLOR COL_HEADING.
WRITE: / 'Sum:', sum, 'Time:', delta COLOR COL_TOTAL.
Dans ce cas on utilise pour chaque sélection une table interne et on boucle dedans. Si on fait un SE30 pour ce code on a le résultat suivant
Commentaire : Dans ce cas on utilise le couple habituel table interne et structure (work area). Cette approche fréquente et classique est aussi la moins performante, tout simplement parcequ'on importe l'ensemble des données.
Mais c'est aussi celle qui permet une maintenance du code facile et rapide.
Stratégie n°2 : On sélectionne toutes les colones avec l'utilisation de SELECT-ENDSELECT.
Le code
REPORT ZBENCHSQL2.
DATA: sflight_wa TYPE sflight,
sbook_wa TYPE sbook,
sum TYPE sbook-loccuram VALUE 0,
t1 TYPE i, t2 TYPE i, delta(16) TYPE p.
GET RUN TIME FIELD t1.
SELECT * FROM sflight INTO sflight_wa
WHERE carrid = 'LH'
AND seatsmax_f = sflight~seatsocc_f.
SELECT * FROM sbook INTO sbook_wa
WHERE carrid = sflight_wa-carrid
AND connid = sflight_wa-connid
AND fldate = sflight_wa-fldate
AND loccurkey = 'EUR'.
sum = sum + sbook_wa-loccuram.
ENDSELECT.
ENDSELECT.
GET RUN TIME FIELD t2.
delta = t2 - t1.
SKIP.
WRITE: / 'SQL 2 : ' COLOR COL_HEADING.
WRITE: / 'Sum:', sum, 'Time:', delta COLOR COL_TOTAL.
On a un SE30 comme celui ci
Commentaire : Dans ce cas on utilise le mécanisme implicite d'un select-endselect dans une work-area, c.à.d que si il y'a plusieurs record le code boucle entre le select et le enselect. C'est quasiment la même approche que dans le cas précédent.
Stratégie n°3 : On ne sélectionne que les champs qu'on désire dans une structure entière
Le code :
REPORT ZBENCHSQL3.
DATA: sflight_wa TYPE sflight,
sbook_wa TYPE sbook,
sum TYPE sbook-loccuram VALUE 0,
t1 TYPE i, t2 TYPE i, delta(16) TYPE p.
GET RUN TIME FIELD t1.
SELECT carrid connid fldate FROM sflight
INTO (sflight_wa-carrid, sflight_wa-connid, sflight_wa-fldate)
WHERE carrid = 'LH'
AND seatsmax_f = sflight~seatsocc_f.
SELECT loccuram FROM sbook INTO sbook_wa-loccuram
WHERE carrid = sflight_wa-carrid
AND connid = sflight_wa-connid
AND fldate = sflight_wa-fldate
AND loccurkey = 'EUR'.
sum = sum + sbook_wa-loccuram.
ENDSELECT.
ENDSELECT .
GET RUN TIME FIELD t2.
delta = t2 - t1.
SKIP.
WRITE: / 'SQL 3 SELECT colonnes et lignes spécifiquement: ' COLOR COL_HEADING.
WRITE: / 'Sum:', sum, 'Time:', delta COLOR COL_TOTAL.
et on a le se30 suivant
Commentaire : Dans ce cas on utilise à nouveau le mécanisme de SELECT-ENDSELECT mais on n'extrait que les champs nécessaire pour les injecter pour SFLIGHT dans une table interne basée sur la structure complète de SFLIGHT mais alimentée que par les données nécessaires. Les autres champs seront vides. Remarquez que dans ce cas la stack ABAP est plus sollicitée que la stack SQL.
Stratégie n°4 : Utilisation d'une table interne et d'un FOR ALL ENTRIES IN
Le code
REPORT ZBENCHSQL4.
DATA: sflight_tab TYPE STANDARD TABLE OF sflight,
sbook_wa TYPE sbook,
sum TYPE sbook-loccuram VALUE 0,
t1 TYPE i, t2 TYPE i, delta(16) TYPE p.
GET RUN TIME FIELD t1.
SELECT carrid connid fldate FROM sflight
INTO CORRESPONDING FIELDS OF TABLE sflight_tab
WHERE carrid = 'LH'
AND seatsmax_f = sflight~seatsocc_f.
SELECT bookid loccuram FROM sbook
INTO (sbook_wa-bookid, sbook_wa-loccuram)
FOR ALL ENTRIES IN sflight_tab
WHERE carrid = sflight_tab-carrid
AND connid = sflight_tab-connid
AND fldate = sflight_tab-fldate
AND loccurkey = 'EUR'.
sum = sum + sbook_wa-loccuram.
ENDSELECT.
GET RUN TIME FIELD t2.
delta = t2 - t1.
SKIP.
WRITE: / 'SQL 4 : ' COLOR COL_HEADING.
WRITE: / 'Sum:', sum, 'Time:', delta COLOR COL_TOTAL.
et on a un se30 de
Commentaire : Dans ce cas nous passons d'abord par une table interne sflight_tab puis nous utilisons un SELECT-ENDSELECT avec l'utilisation d'un FOR ALL ENTRIES .... C'est un mécanisme fourni par ABAP SQL pratique mais couteux en performance.
Stratégie n° 5 : Utilisation d'une fonction SUM
Le code :
REPORT ZBENCHSQL5.
DATA: sflight_wa TYPE sflight,
sbook_wa TYPE sbook,
sum TYPE sbook-loccuram VALUE 0,
t1 TYPE i, t2 TYPE i, delta(16) TYPE p.
GET RUN TIME FIELD t1.
sum = 0.
SELECT carrid connid fldate FROM sflight
INTO (sflight_wa-carrid, sflight_wa-connid, sflight_wa-fldate)
WHERE carrid = 'LH'
AND seatsmax_f = sflight~seatsocc_f.
SELECT SUM( loccuram ) FROM sbook INTO sbook_wa-loccuram
WHERE carrid = sflight_wa-carrid
AND connid = sflight_wa-connid
AND fldate = sflight_wa-fldate
AND loccurkey = 'EUR'.
sum = sum + sbook_wa-loccuram.
ENDSELECT.
GET RUN TIME FIELD t2.
delta = t2 - t1.
SKIP.
WRITE: / 'SQL 5 : ' COLOR COL_HEADING.
WRITE: / 'Sum:', sum, 'Time:', delta COLOR COL_TOTAL.
On a un SE30 de
Commentaire : Dans ce cas là on confit le calcul de la somme à une fonction SQL pour chaque select dans sbook et on somme la somme des sommes de SBOOK en ABAP (vous suivez ? moi limite ).
Stratégie n°6 : Utilisation d'un INNER-JOIN et d'une fonction SUM.
Le code :
REPORT ZBENCHSQL6.
DATA: sum TYPE sbook-loccuram VALUE 0,
t1 TYPE i,
t2 TYPE i, delta(16) TYPE p.
GET RUN TIME FIELD t1.
SELECT SUM( loccuram )
FROM sflight AS f INNER JOIN sbook AS b
ON f~carrid = b~carrid AND
f~connid = b~connid AND
f~fldate = b~fldate
INTO sum
WHERE f~carrid = 'LH'
AND f~seatsmax_f = f~seatsocc_f
AND loccurkey = 'EUR'.
GET RUN TIME FIELD t2.
delta = t2 - t1.
SKIP.
WRITE: / 'SQL 6 : ' COLOR COL_HEADING.
WRITE: / 'Sum:', sum, 'Time:', delta COLOR COL_TOTAL.
et on a un SE30 de
Commentaire : Dans ce cas on utilise une fonction SQL SUM et un INNER JOIN.
Stratégie n°7 : Proposé par Sébastien (Merci à toi)
Le code :
DATA: sflight_wa TYPE sflight,
sbook_wa TYPE sbook,
sum TYPE sbook-loccuram VALUE 0,
t1 TYPE i, t2 TYPE i, delta(16) TYPE p.
GET RUN TIME FIELD t1.
sum = 0.
SELECT carrid connid fldate FROM sflight
INTO (sflight_wa-carrid, sflight_wa-connid, sflight_wa-fldate)
WHERE carrid = 'LH'
AND seatsmax_f = sflight~seatsocc_f.
SELECT SUM( loccuram ) FROM sbook INTO sbook_wa-loccuram
WHERE carrid = sflight_wa-carrid
AND connid = sflight_wa-connid
AND fldate = sflight_wa-fldate
AND loccurkey = 'EUR'.
sum = sum + sbook_wa-loccuram.
ENDSELECT.
GET RUN TIME FIELD t2.
delta = t2 - t1.
SKIP.
WRITE: / 'SQL 5 : ' COLOR COL_HEADING.
WRITE: / 'Sum:', sum, 'Time:', delta COLOR COL_TOTAL.
Conclusion : Tout dépend des serveurs.
Sur une machine unique portant autant le serveur SAP que la base de donnée voila les performances que nous avons. La solution 3 est la meilleure.
On se rend compte que la stratégie n°3 gagne largement. Cela s'explique par plusieurs raisons :
- On réduit le nombre d'information échangée entre SAP et la base de donnée en allant chercher que ce qu'il y'a de nécessaire.
- On s'appuie surtout sur le stack ABAP et non SQL.
Mais Sébastien obtient d'autres résultats
En fait tout dépend de la performance et du serveur d'application SAP et du serveur de base de donnée. Si la DB est trés performante l'INNER-JOIN sera la solution, mais dans un cas plus équilibré ce sera souvent la solution 3 qui sera la plus performante. Mais le plus simple est de tester. C'est pour cela que je vous propose les sources dans la section download.
Vos commentaires sont attendus.
Jerome Fortias