Performances SQL : traitements ensemblistes et ligne à ligne, la métaphore de la boulangère

Introduction

Il nous arrive de plus en plus fréquemment d'avoir à expliquer la différence entre traitement ensembliste et traitement ligne à ligne en base de données. Sans doute une résultante de la mode de ces dernières années tendant à considérer un moteur de base de données comme un simple conteneur. Certaines méthodes de programmation objet valident et accompagnent d'ailleurs cette approche .

L'origine du dialogue est souvent la même : un traitement devient lent et quand le dba a identifié une cause de la lenteur, que 3 fois sur 4 elle correspond au cas qui nous occupe, et qu'il ne peut fournir de réponse immédiate, on se trouve dans une situation difficile, l'un parlant de méthode, l'autre évoquant performance du moteur, indexes, cpu, mémoire, réseau, verrouillage, statistiques etc.., qui sont des paramètres relativement aisés à manipuler mais hélas rarement en phase avec la vraie nature du problème.

La métaphore de la boulangère a pour but de présenter la différence entre deux méthodes d'accès aux données: ligne-à-ligne et ensembliste. Elle a ceci d'intéressant que l'auditeur comprend de lui-même la problématique avant la fin de l'histoire. Cela rend alors beaucoup plus simple l'analyse et la recherche de solutions.

Un petit test chiffré va compléter la démonstration.

L'histoire de la boulangère

Chaque jour Bettina va chercher son pain

Elle y va d'autant plus joyeusement que le boulanger fabrique un pain excellent, que la boulangère une copine et que l'établissement est juste en face de chez elle.

Aussi, chaque soir, elle descend deux étages, traverse la rue ( elle fait attention ), ouvre la porte, salue comme il se doit les personnes présentes, attend son tour, passe commande, paye, prend son pain, récupère la monnaie, salue sa copine, se retourne, ouvre la porte, traverse la rue, monte ses 2 étages et embrasse son mari.

Jusqu'ici tout va bien. C'est alors que survient le drame : un jour, ou peut être une nuit, Bettina, au lieu de s'endormir reçoit du monde. Soirée Sandwich. Elle a besoin de 3 baguettes.

Que faire ? conserver ses habitudes, et descendre 3 fois demander 1 pain ou faire une folie, et demander les 3 baguettes d'un coup ?

Descendre 3 fois est une option sympatique car ça fait faire un peu d'exercice et donne 3 occasions différentes de papoter avec sa copine. Le problème est que c'est un peu long et risqué. Il faut traverser 6 fois la rue, monter et descendre 3 fois les escaliers sachant que Bettina n'a pas de garantie qu'il reste du pain lors de la 2ème et 3ème visite.

La deuxième option est séduisante : elle est moins fatiguante et permet à Bettina de profiter plus de son chéri. Par contre il faut qu'elle change de sac, celui qu'elle prend d'habitude n'est pas prévu pour.

Et vous, que feriez-vous ?

Décodage

On peut utiliser de nombreux éléments de cette histoire pour évoquer des correspondances techniques, par exemple :

Élément de l'histoire Parallèle technique possible
... descend 2 etages ... Début du programme
... traverse la rue ... Connexion, négociation / protocole
... ouvre la porte ... Authentification
... attend son tour .. Verrouillage
... demande son pain ... Requêtage
... prend son pain et sa monnaie ... Récupération du résultat
... se retourne, ouvre la porte ... Libération des ressources
... traverse la rue ... Déconnexion
... monte ses 2 étages ... Fin du programme

Un exemple chiffré

L'exemple ci-dessous a été réalisé avec Sybase ASE 15.0.3.

Il consiste à interroger une table complexe volumineuse de plusieurs manières différentes, de la plus unitaire à la plus ensembliste.

Les résultats sont donnés en I/Os, la durée en ms indiquée est informative.

Contexte

Table INVESTMENT_POSITION, 160 millions de lignes pour 23 Go

execute sp_spaceused INVESTMENT_POSITION
goname                rowtotal  reserved    data        index_size unused   
------------------- --------- ----------- ----------- ---------- ---------
INVESTMENT_POSITION 160759951 22901136 KB 14859384 KB 7741342 KB 300410 KB

Une clé primaire cluster sur les 3 colonnes (PORTFOLIO_ID, INSTRUMENT_ID et DATE) + 2 indexes.

La répartition des données est plutôt homogène et les valeurs distinctes de la clé primaire représentées comme suit :

Valeurs distinctes

PORTFOLIO_ID INSTRUMENT_ID DATE
1772 191584 2663

On se propose de mesurer le comportement de plusieurs variations de la même requête.

Le but est de récupérer une valeur pour le PORTFOLIO_ID=486 sur une periode allant du 01/05/2008 au 08/05/2008 pour une dizaine d'INSTRUMENT_ID ( 2702,2703,2707,2709,2711,2713,2714,2718,2720,2731)

Test # 1 : méthode ligne à ligne

L'information est demandée date par date, instrument par instrument, soit 80 requêtes au total ( 1 PORTFOLIO_ID x 10 INSTRUMENT_ID x 8 DATE ) :

set showplan on
set statistics io on
go

select  PHYSICAL_QUANTITY 
from    INVESTMENT_POSITION 
where   PORTFOLIO_ID=486 
and     INSTRUMENT_ID=2702
and     DATE='20080501' 
goQUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        1 operator(s) under root

       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  INVESTMENT_POSITION
       |   |  Using Clustered Index.
       |   |  Index : PK_INVESTMENT_POSITION
       |   |  Forward Scan.
       |   |  Positioning by key.
       |   |  Keys are:
       |   |    PORTFOLIO_ID ASC
       |   |    INSTRUMENT_ID ASC
       |   |    DATE ASC
       |   |  Using I/O Size 2 Kbytes for data pages.
       |   |  With LRU Buffer Replacement Strategy for data pages.


Table: INVESTMENT_POSITION scan count 1, logical reads: (regular=5 apf=0 total=5), ...

Le plan d'exécution montre le bon usage de la clé primaire unique et 5 I/Os par requête, soit la hauteur de l'index.

Au total, l'opération va necessiter 80 scans pour 404 IOs.

Test # 2 : méthode semi-ensembliste par INSTRUMENT_ID

L'information est demandée date par date, pour l'ensemble des instruments, soit 8 requêtes ( 1 PORTFOLIO_ID x 1 liste de 10 INSTRUMENT_ID x 8 DATE ) :

set showplan on
set statistics io on
go

select PHYSICAL_QUANTITY 
from   INVESTMENT_POSITION 
where  PORTFOLIO_ID=486 
and    INSTRUMENT_ID in ( 2702,2703,2707,2709,2711,2713,2714,2718,2720,2731 )
and    DATE='20080501' 
goQUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        4 operator(s) under root

       |ROOT:EMIT Operator (VA = 4)
       |
       |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM OR List
       |   |   |  OR List has up to 10 rows of OR/IN values.
       |   |
       |   |   |RESTRICT Operator (VA = 2)(0)(0)(0)(1)(0)
       |   |   |
       |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |  FROM TABLE
       |   |   |   |  INVESTMENT_POSITION
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Index : PK_INVESTMENT_POSITION
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Keys are:
       |   |   |   |    PORTFOLIO_ID ASC
       |   |   |   |    INSTRUMENT_ID ASC
       |   |   |   |    DATE ASC
       |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.


Table: INVESTMENT_POSITION scan count 10, logical reads: (regular=51 apf=0 total=51),...

Il s'agit d'un plan équivalent au test #1 dans la mesure où les 3 colonnes de la clé primaire sont bien sollicitées.

L'optimiseur transforme la liste IN ( ) en OR , stratégie similaire à la méthode ligne à ligne soit une cinquantaine d'I/Os par requête.

Au total, l'opération va nécessiter 80 scans pour 408 I/Os.

Test # 3 : méthode semi-ensembliste par DATE

L'information est demandée instrument par instrument, pour l'ensemble des dates, soit 10 requêtes ( 1 PORTFOLIO_ID x 10 INSTRUMENT_ID x 1 liste de 8 DATE ) de type:

set showplan on
set statistics io on
go

select         PHYSICAL_QUANTITY 
from         INVESTMENT_POSITION 
where         PORTFOLIO_ID=486 
and         DATE between '20080501' and '20080508' 
and         INSTRUMENT_ID = 2702
goQUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        1 operator(s) under root

       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  INVESTMENT_POSITION
       |   |  Using Clustered Index.
       |   |  Index : PK_INVESTMENT_POSITION
       |   |  Forward Scan.
       |   |  Positioning by key.
       |   |  Keys are:
       |   |    PORTFOLIO_ID ASC
       |   |    INSTRUMENT_ID ASC
       |   |    DATE ASC
       |   |  Using I/O Size 2 Kbytes for data pages.
       |   |  With LRU Buffer Replacement Strategy for data pages.


Table: INVESTMENT_POSITION scan count 1, logical reads: (regular=5 apf=0 total=5) ...

Le plan d'exécution est identique au plan d'exécution du Test #1 : le fait de définir les 2 premières colonnes de l'index cluster rend l'opération très efficace.

Au total, 54 I/Os pour 10 scans. Dans le cas présent, on bénéficie de l'index cluster qui est doublement discriminant pour la colonne PORTFOLIO_ID et la colonne l'INSTRUMENT_ID.

L'opération est 8 fois plus rapide que lors du test #1 car pour autant d'I/Os 8 lignes sont extraites au lieu de 1.

Test # 4 : méthode ensembliste

La demande est réalisée en une seule commande :

set showplan on
set statistics io on
go

select  PHYSICAL_QUANTITY 
from    INVESTMENT_POSITION  
where   PORTFOLIO_ID=486 
and     INSTRUMENT_ID in ( 2702,2703,2707,2709,2711,2713,2714,2718,2720,2731 )
and     DATE between '20080501' and '20080508'QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        4 operator(s) under root

       |ROOT:EMIT Operator (VA = 4)
       |
       |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM OR List
       |   |   |  OR List has up to 10 rows of OR/IN values.
       |   |
       |   |   |RESTRICT Operator (VA = 2)(0)(0)(0)(1)(0)
       |   |   |
       |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |  FROM TABLE
       |   |   |   |  INVESTMENT_POSITION
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Index : PK_INVESTMENT_POSITION
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Keys are:
       |   |   |   |    PORTFOLIO_ID ASC
       |   |   |   |    INSTRUMENT_ID ASC
       |   |   |   |    DATE ASC
       |   |   |   |  Using I/O Size 2 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.


Table: INVESTMENT_POSITION scan count 10, logical reads: (regular=54 apf=0 total=54) ...

On retrouve le plan du test #2, soit la liste des instruments transformée en OR et l'usage des 3 colonnes de l'index cluster.

Au total 54 I/Os pour 1 seul scan.

Le gain est obtenu ici en réduisant drastiquement les échanges des paquets réseau, le parse et la compilation des requêtes.

Bilan

Test Type # Commandes SQL Scans Total I/Os Durée pour 1000 exécutions* (secondes)
Test #1 Ligne à ligne 80 80 404 18,63
Test #2 Semi-ensembliste 8 80 408 5,29
Test #3 Semi-ensembliste 10 10 54 3,90
Test #4 Ensembliste 1 10 54 1,75

* les temps d'exécution étant atomiques, chacune des méthodes a été executée 1000 fois consécutivement , en 1 seul appel par isql.

Dans le cadre de ce test, il est démontré qu'une démarche ligne à ligne pouvait être 10 fois plus lente qu'une démarche ensembliste. Et encore, seule les opérations en base ont été mesurées ici.

En tenant compte des intéractions réseau (remplissage de paquets réseau / coût du protocole) , de la logique applicative qui sous-tend la démarche unitaire, les durées de traitements mesurées seront encore bien pire.

Par ailleurs, les aspects transactionnels ne sont pas évoqués ici : la gestion de la contention, la pose de verrous et autres paramètres influant sur les performances globales d'une application.