Variantes d'écriture d'une requête SQL simple

28 octobre 2007
web server sql

Présentation

Pendant la lecture du chapitre 6, pages 126 et 127, du livre de Joe Celko "Sql Programming Style", Elsevier, 2005, j'ai eu envie de vérifier un schéma et une requête qu'il exécute sur un certain type de données.

Les données sont des prêts pour lesquels on stocke des paiements successifs qui peuvent avoir chacun un status (S, U ou F) qui détermine, si on veut, un paiement a échéance, en retard ou envoyé (la signification de ce status n'a pas d'importance). Il cherche, étant donné ce schéma, la liste des prêts dont TOUS les paiements ont le status 'F'.

Simple ? Oui, mais on peut diverger quand à la construction des requêtes possibles pour trouver ce résultat.

Le schéma et la solution de Celko

Son schéma, que je reprend, est le suivant :

CREATE TABLE loans (
        loanId int not null,
        paymentId int not null,
        paymentStatus = ENUM('F', 'U', 'S'),
        primary key (loanId, paymentId)
    )

Sa solution est élégante et rapide (cf ci-dessous) et je voulais savoir si on pouvais faire mieux. Tout d'abord présentons sa solution :

select loanId, count(*) as nbPayments
        FROM loans
        GROUP BY loanId
        HAVING MAX(paymentStatus)='F' and MIN(paymentStatus)='F';

On parcourt donc la totalité de la table et on extrait bien les prêts dont les paiements n'ont pas d'autres statuts que 'F'.

Les alternatives

J'ai deux solutions alternatives. La première utilise deux sous-requêtes, une qui donne pour chaque prêts le nombre de paiements ayant le statuts 'F' et une autre qui donne le nombre de paiements total. Une clause WHERE est présente pour ne garder que les prêts pour lesquels ces décomptes sont égaux.

select GlobalStats.loanId, GlobalStats.nbPayments
        from
        (select loanId, count(*) as nbPayments from loans where paymentStatus='F' group by loanId) as FStats,
        (select loanId, count(*) as nbPayments from loans group by loanId) as GlobalStats
        where FStats.loanId=GlobalStats.loanId and FStats.nbPayments=GlobalStats.nbPayments;

La deuxième solution calcule directement pour chaque prêt le nombre de paiements et le nombre de paiements ayant le statut 'F', en utilisant la somme d'une valeur issue d'un IF.

select loanId, nbPayments
        from (
            select loanId, count(loanId) as nbPayments, sum(if(paymentStatus='F', 1, 0)) as nbFPayments from loans group by loanId
        ) as inter
        where inter.nbPayments=inter.nbFPayments;

Conclusion

Le résultat ? Déprimant ! Mes deux solutions peuvent être plus simples à comprendre au premier coup d'oeil mais sont jusqu'à 30% plus lente à l'exécution, alors que la solution de Celko qui peut surprendre au premier regard, est en fait très simple à comprendre, plus rapide et plus constante dans son temps d'exécution.

Pour tester ça j'ai utilisé un petit script PHP qui initialise la table, la remplit avec des milliers de données, règle certains prêts de manière à ce qu'ils apparaissent dans le résultat des requêtes de cette page et compare les temps d'exécution des trois variantes.

C'est moi ou Joe Celko ressemble drôlement à Anton Lavey ?
Accueil