BENCHMARK BDE entre requêtes SQL et QBE

BENCHMARK BDE entre requêtes SQL et QBE

Conditions :

table CLIENT : 5 000 occurrences
table COMMANDE : 50 000 occurrences
table LIGNE_PRODUIT : 500 000 occurrences

Volume de la base : 100 Mo

Effectué sur PENTIUM 2 - 400Mhz doté de 128 Mo de RAM, utilisateur unique,
requêtes "à chaud".

N° Type Libellé Début Fin Durée Gain de
SQL/QBE

seconde %

1.1 QBE les clients dont le code postal est 75001 15:24:36 15:24:37 0,13

1.1 SQL les clients dont le code postal est 75001 15:24:33 15:24:33 0,04 0,09 69%

1.2 QBE les commandes datant du 3/5/1998 15:24:43 15:24:43 0,20

1.2 SQL les commandes datant du 3/5/1998 15:24:47 15:24:47 0,04 0,16 80%

1.3 QBE Les lignes de produit du fabriquant CANON 15:36:11 15:36:16 4,35

1.3 SQL Les lignes de produit du fabriquant CANON 15:36:50 15:36:55 5,73 -1.38 -32%

2.1 QBE les clients dont l'adresse contient le mot 'rue' avec un indice de satisfaction > 0,8 et < 0,9 15:00:20 15:00:20 0,11

2.1 SQL les clients dont l'adresse contient le mot 'rue' avec un indice de satisfaction > 0,8 et < 0,9 15:00:27 15:00:27 0,12 -0.01 -9%

2.2 QBE les commandes payés par 'CHQ' dont le montant est > 250000 et < 260000 15:00:36 15:00:37 0,67

2.2 SQL les commandes payés par 'CHQ' dont le montant est > 250000 et < 260000 15:00:48 15:00:48 0,71 -0.04 -6%

2.3 QBE les lignes de produit dont le prix de vente est < 2000, la quantité > 10 et la remise = 10% 15:37:10 15:37:12 1,77

2.3 SQL les lignes de produit dont le prix de vente est < 2000, la quantité > 10 et la remise = 10% 15:37:21 15:37:23 1,80 -0.03 -2%

3.1 QBE le nombre de clients entrés au mois de juin 15:01:36 15:01:36 0,20

3.1 SQL le nombre de clients entrés au mois de juin 15:01:44 15:01:44 0,19 0.01 5%

3.2 QBE la somme des commandes du mois de juillet 1998 15:01:53 15:01:54 0,23

3.2 SQL la somme des commandes du mois de juillet 1998 15:02:00 15:02:00 0,17 0.06 26%

3.3 QBE la moyenne des quantités des produits vendu pour la marque CANON 15:37:38 15:37:43 4,22

3.3 SQL la moyenne des quantités des produits vendu pour la marque CANON 15:37:52 15:37:57 4,80 -0.58 -14%

4.1 QBE les clients ayant commandés au mois d'avril 1998 pour un montant > 500000 15:03:05 15:03:05 0,34

4.1 SQL les clients ayant commandés au mois d'avril 1998 pour un montant > 500000 15:03:13 15:03:13 0,43 -0.09 -26%

4.2 QBE les commandes du 4eme trimestre 1998 portant sur des clients dont le nom commence par L et domiciliés à Paris 15:03:20 15:03:21 0,36

4.2 SQL les commandes du 4eme trimestre 1998 portant sur des clients dont le nom commence par L et domiciliés à Paris 15:03:27 15:03:27 0,47 -0.11 -31%

4.3 QBE les lignes de produit du fabriquant CANON dont la quantité = 10 et la date de commande en septembre (quelque soit l'année) 15:38:13 15:38:18 4,81

4.3 SQL les lignes de produit du fabriquant CANON dont la quantité = 10 et la date de commande en septembre (quelque soit l'année) 15:38:35 15:38:40 5,08 -0.27 -6%

5.1 QBE Les clients avec CP = 75.. ayant une commande dont la date d'échéance dépasse de 60 jour la date de commande, avec des lignes de produit du fabriquant CANON 15:39:17 15:39:22 4,74

5.1 SQL Les clients avec CP = 75.. ayant une commande dont la date d'échéance dépasse de 60 jour la date de commande, avec des lignes de produit du fabriquant CANON 15:39:38 15:39:43 5,39 -0.65 -14%

6.1 QBE Les clients du 92 n'ayant pas commandé au mois de janvier 15:40:07 15:40:18 10,25

6.1 SQL Les clients du 92 n'ayant pas commandé au mois de janvier 15:40:24 15:40:25 0,87 9.38 92%

6.2 QBE Le montant TTC des commandes du mois de février 1998 pour les clients domiciliés dans le 13, ayant au plus 1 article du fabriquant CANON 17:05:01 17:05:06 5,30

6.2 SQL Le montant TTC des commandes du mois de février 1998 pour les clients domiciliés dans le 13, ayant au plus 1 article du fabriquant CANON 17:45:34 17:45:41 6,52 -1.22 -23%

Total QBE 37,68

Total SQL 32,36

Dans certains cas les écarts entre SQL et QBE montrent une différence de temps de l'ordre de 25 à 30 % en faveur de QBE, mais dans certaines requêtes très simples (1.1, 1.2) ou très complexes (6.1) la différence est très importante et nettement en faveur de SQL.
Notons qu'aucune requête SQL n'a été optimisée dans son écriture ce qui aurait permis encore quelques gains de temps.
Dès que l'utilisation de partie de dates est nécessaire ou bien que des requêtes ensemblistes (groupage) sont nécessaire, SQL affirme sa suprématie.

NOTA : les requêtes ont été traduite de QBE vers SQL de la manière la plus simpliste, en partie par la traduction SQL disponible dans Paradox 7 et cela sans aucune optimisation. Par exemple le mot clef DISTINCT a été introduit dans la clause SELECT de toutes les requêtes afin que cela corresponde à ce que fait QBE par défaut, de même que la clause ORDER BY. Or le distinct n'est pas toujours nécessaire, ni le ORDER BY. Quelques optimisations sont donc possibles, et hormis le DISTINCT / ORDER BY à supprimer on pourra par exemple utiliser le BETWEEN au lieu d'une fourchette de valeur < >.

EN CONCLUSION : la match QBE/SQL n'est pas très probant, même si SQL semble l'emporter d'une courte tête. Mais bien optimisé il y a fort à parier que la différence en faveur de SQL devient plus sensible. On préférera donc utiliser SQL plus rapide sur de gros volumes et des requêtes complexes, et plus portable pour le passage au client/serveur.

LES FICHIERS
Les fichiers constituant le test peuvent être envoyé sur demande (quelques jours après l'envoi du mail).
Dans ce cas, cliquez sur le lien suivant :
Demande d'envoi des fichiers test
ATTENTION : cet envoi zippé est constitué de 9 fichiers dont le plus gros fait 16 Mo. Vérifiez que votre FAI accepte une telle volumétrie avant toute demande.

`N°`	`Type`	`Libellé`	`Début`	`Fin`	`Durée`	`Gain de` `SQL/QBE`
`N°`	`Type`	`Libellé`	`Début`	`Fin`	`Durée`	`seconde`	`%`
`1.1`	`QBE`	`les clients dont le code postal est 75001`	`15:24:36`	`15:24:37`	`0,13`
`1.1`	`SQL`	`les clients dont le code postal est 75001`	`15:24:33`	`15:24:33`	`0,04`	`0,09`	`69%`
`1.2`	`QBE`	`les commandes datant du 3/5/1998`	`15:24:43`	`15:24:43`	`0,20`
`1.2`	`SQL`	`les commandes datant du 3/5/1998`	`15:24:47`	`15:24:47`	`0,04`	`0,16`	`80%`
`1.3`	`QBE`	`Les lignes de produit du fabriquant CANON`	`15:36:11`	`15:36:16`	`4,35`
`1.3`	`SQL`	`Les lignes de produit du fabriquant CANON`	`15:36:50`	`15:36:55`	`5,73`	`-1.38`	`-32%`
`2.1`	`QBE`	`les clients dont l'adresse contient le mot 'rue' avec un indice de satisfaction > 0,8 et < 0,9`	`15:00:20`	`15:00:20`	`0,11`
`2.1`	`SQL`	`les clients dont l'adresse contient le mot 'rue' avec un indice de satisfaction > 0,8 et < 0,9`	`15:00:27`	`15:00:27`	`0,12`	`-0.01`	`-9%`
`2.2`	`QBE`	`les commandes payés par 'CHQ' dont le montant est > 250000 et < 260000`	`15:00:36`	`15:00:37`	`0,67`
`2.2`	`SQL`	`les commandes payés par 'CHQ' dont le montant est > 250000 et < 260000`	`15:00:48`	`15:00:48`	`0,71`	`-0.04`	`-6%`
`2.3`	`QBE`	`les lignes de produit dont le prix de vente est < 2000, la quantité > 10 et la remise = 10%`	`15:37:10`	`15:37:12`	`1,77`
`2.3`	`SQL`	`les lignes de produit dont le prix de vente est < 2000, la quantité > 10 et la remise = 10%`	`15:37:21`	`15:37:23`	`1,80`	`-0.03`	`-2%`
`3.1`	`QBE`	`le nombre de clients entrés au mois de juin`	`15:01:36`	`15:01:36`	`0,20`
`3.1`	`SQL`	`le nombre de clients entrés au mois de juin`	`15:01:44`	`15:01:44`	`0,19`	`0.01`	`5%`
`3.2`	`QBE`	`la somme des commandes du mois de juillet 1998`	`15:01:53`	`15:01:54`	`0,23`
`3.2`	`SQL`	`la somme des commandes du mois de juillet 1998`	`15:02:00`	`15:02:00`	`0,17`	`0.06`	`26%`
`3.3`	`QBE`	`la moyenne des quantités des produits vendu pour la marque CANON`	`15:37:38`	`15:37:43`	`4,22`
`3.3`	`SQL`	`la moyenne des quantités des produits vendu pour la marque CANON`	`15:37:52`	`15:37:57`	`4,80`	`-0.58`	`-14%`
`4.1`	`QBE`	`les clients ayant commandés au mois d'avril 1998 pour un montant > 500000`	`15:03:05`	`15:03:05`	`0,34`
`4.1`	`SQL`	`les clients ayant commandés au mois d'avril 1998 pour un montant > 500000`	`15:03:13`	`15:03:13`	`0,43`	`-0.09`	`-26%`
`4.2`	`QBE`	`les commandes du 4eme trimestre 1998 portant sur des clients dont le nom commence par L et domiciliés à Paris`	`15:03:20`	`15:03:21`	`0,36`
`4.2`	`SQL`	`les commandes du 4eme trimestre 1998 portant sur des clients dont le nom commence par L et domiciliés à Paris`	`15:03:27`	`15:03:27`	`0,47`	`-0.11`	`-31%`
`4.3`	`QBE`	`les lignes de produit du fabriquant CANON dont la quantité = 10 et la date de commande en septembre (quelque soit l'année)`	`15:38:13`	`15:38:18`	`4,81`
`4.3`	`SQL`	`les lignes de produit du fabriquant CANON dont la quantité = 10 et la date de commande en septembre (quelque soit l'année)`	`15:38:35`	`15:38:40`	`5,08`	`-0.27`	`-6%`
`5.1`	`QBE`	`Les clients avec CP = 75.. ayant une commande dont la date d'échéance dépasse de 60 jour la date de commande, avec des lignes de produit du fabriquant CANON`	`15:39:17`	`15:39:22`	`4,74`
`5.1`	`SQL`	`Les clients avec CP = 75.. ayant une commande dont la date d'échéance dépasse de 60 jour la date de commande, avec des lignes de produit du fabriquant CANON`	`15:39:38`	`15:39:43`	`5,39`	`-0.65`	`-14%`
`6.1`	`QBE`	`Les clients du 92 n'ayant pas commandé au mois de janvier`	`15:40:07`	`15:40:18`	`10,25`
`6.1`	`SQL`	`Les clients du 92 n'ayant pas commandé au mois de janvier`	`15:40:24`	`15:40:25`	`0,87`	`9.38`	`92%`
`6.2`	`QBE`	`Le montant TTC des commandes du mois de février 1998 pour les clients domiciliés dans le 13, ayant au plus 1 article du fabriquant CANON`	`17:05:01`	`17:05:06`	`5,30`
`6.2`	`SQL`	`Le montant TTC des commandes du mois de février 1998 pour les clients domiciliés dans le 13, ayant au plus 1 article du fabriquant CANON`	`17:45:34`	`17:45:41`	`6,52`	`-1.22`	`-23%`
`Total`	`QBE`				`37,68`
`Total`	`SQL`				`32,36`

`1.1`	`QBE`	`Query` `Client.db \| NO_CLI \| CP_CLI \|` `\| Check \| 75001 \|` `EndQuery`
	`SQL`	`SELECT DISTINCT NO_CLI` `FROM Client` `WHERE CP_CLI = '75001'` `ORDER BY NO_CLI`
`1.2`	`QBE`	`Query` `commande.DB \| NO_COM \| DATE_COM \|` `\| Check \| 03/05/1998 \|` `EndQuery`
	`SQL`	`SELECT DISTINCT NO_COM` `FROM commande` `WHERE DATE_COM = '05/03/1998'` `ORDER BY NO_COM`
`1.3`	`QBE`	`Query` `lignProd.DB \| NO_COM \| FABRIQUANT_PROD \|` `\| Check \| CANON \|` `EndQuery`
	`SQL`	`SELECT DISTINCT NO_COM, REF_PROD` `FROM lignProd` `WHERE FABRIQUANT_PROD = 'CANON'` `ORDER BY NO_COM, REF_PROD`
`2.1`	`QBE`	`Query` `Client.db \| NO_CLI \| ADRESSE1_CLI \| SATISFACTION_CLI \|` `\| Check \| ..rue.. \| >8/10, <9/10 \|` `EndQuery`
	`SQL`	`SELECT DISTINCT NO_CLI` `FROM Client.db` `WHERE ADRESSE1_CLI LIKE '%Rue%'` `AND SATISFACTION_CLI > (0.8)` `AND SATISFACTION_CLI < (0.9)` `ORDER BY NO_CLI`
`2.2`	`QBE`	`Query` `commande.DB \| NO_COM \| MODE_PAIEMENT_COM \| MONTANT_HT_COM \|` `\| Check \| CHQ \| Check >250000, <260000 \|` `EndQuery`
	`SQL`	`SELECT DISTINCT NO_COM, MONTANT_HT_COM` `FROM commande.DB` `WHERE MODE_PAIEMENT_COM = 'CHQ'` `AND MONTANT_HT_COM > 250000.0` `AND MONTANT_HT_COM < 260000.0` `ORDER BY NO_COM, MONTANT_HT_COM`
`2.3`	`QBE`	`Query` `lignProd.DB \| NO_COM \| REF_PROD \| PRIX_VENTE_HT_PROD \| QUANTITE_PROD \|` `\| Check \| Check \| <2000 \| >10 \|` `lignProd.DB \| REMISE_PROD \|` `\| 10 \|` `EndQuery`
	`SQL`	`SELECT DISTINCT NO_COM, REF_PROD` `FROM lignProd.DB` `WHERE PRIX_VENTE_HT_PROD < 2000.0)` `AND QUANTITE_PROD > 10.0` `AND REMISE_PROD = 10.0` `ORDER BY NO_COM, REF_PROD`
`3.1`	`QBE`	`Query` `Client.db \| NO_CLI \| DATE_ENTREE_CLI \|` `\| calc count \| @@/06/@@@@ \|` `EndQuery`
	`SQL`	`SELECT COUNT (*)` `FROM Client.db"` `WHERE EXTRACT(MONTH FROM DATE_ENTREE_CLI) = 6`
`3.2`	`QBE`	`Query` `commande.DB \| DATE_COM \| MONTANT_HT_COM \|` `\| >= 01/07/1998, <= 31/07/1998 \| calc sum \|` `EndQuery`
	`SQL`	`SELECT SUM(MONTANT_HT_COM)` `FROM commande.DB` `WHERE DATE_COM >= '07/01/1998'` `AND DATE_COM <= '07/31/1998'`
`3.3`	`QBE`	`Query` `lignProd.DB \| FABRIQUANT_PROD \| QUANTITE_PROD \|` `\| CANON \| calc average \|` `EndQuery`
	`SQL`	`SELECT AVG(QUANTITE_PROD)` `FROM lignProd.DB` `WHERE FABRIQUANT_PROD = 'CANON'`
`4.1`	`QBE`	`Query` `commande.DB \| NO_CLI \| DATE_COM \| MONTANT_HT_COM \|` `\| _jointure1 \| >=01/04/1998, <=30/04/1998 \| >500000 \|` `Client.db \| NO_CLI \|` `\| Check _jointure1 \|` `EndQuery`
	`SQL`	`SELECT DISTINCT d1.NO_CLI` `FROM commande.DB" D, "Client.db" d1` `WHERE D.DATE_COM >= '04/01/1998'` `AND D.DATE_COM <= '04/30/1998'` `AND D.MONTANT_HT_COM > 500000.0` `AND d1.NO_CLI = D.NO_CLI` `ORDER BY d1.NO_CLI`
`4.2`	`QBE`	`Query` `commande.DB \| NO_CLI \| NO_COM \| DATE_COM \|` `\| _jointure1 \| Check \| >=1/10/1998, <31/12/1998 \|` `Client.db \| NO_CLI \| NOM_CLI \| VILLE_CLI \|` `\| _jointure1 \| L.. \| PARIS \|` `EndQuery`
	`SQL`	`SELECT DISTINCT D.NO_COM` `FROM commande.DB D, Client.db d1` `WHERE D.DATE_COM >= '10/01/1998'` `AND D.DATE_COM < '12/31/1998'` `AND d1.NO_CLI = D.NO_CLI` `AND d1.NOM_CLI LIKE 'L%'` `AND d1.VILLE_CLI = 'PARIS'` `ORDER BY D.NO_COM`
`4.3`	`QBE`	`Query` `lignProd.DB \| NO_COM \| REF_PROD \| FABRIQUANT_PROD \| QUANTITE_PROD \|` `\| Check _jointure1 \| Check \| CANON \| 10 \|` `commande.DB \| NO_COM \| DATE_COM \|` `\| _jointure1 \| @@/09/@@@@ \|` `EndQuery`
	`SQL`	`SELECT DISTINCT D.NO_COM, D.REF_PROD` `FROM lignProd.DB D, commande.DB D1` `WHERE D.FABRIQUANT_PROD = 'CANON'` `AND D.QUANTITE_PROD = 10.0` `AND D1.NO_COM = D.NO_COM` `AND EXTRACT( MONTH FROM DATE_COM) = 9` `ORDER BY D.NO_COM, D.REF_PROD`
`5.1`	`QBE`	`Query` `lignProd.DB \| NO_COM \| FABRIQUANT_PROD \|` `\| _jointure2 \| CANON \|` `commande.DB \| NO_CLI \| NO_COM \| DATE_COM \| DATE_ECHEANCE_COM \|` `\| _jointure1 \| _jointure2 \| _dc \| >_dc+60 \|` `Client.db \| NO_CLI \| CP_CLI \|` `\| Check _jointure1 \| 75.. \|` `EndQuery`
	`SQL`	`SELECT DISTINCT d2.NO_CLI` `FROM lignProd.DB D, commande.DB D1, Client.db d2` `WHERE D.FABRIQUANT_PROD = 'CANON'` `AND D1.NO_COM = D.NO_COM` `AND D1.DATE_ECHEANCE_COM > D1.DATE_COM + 60` `AND d2.NO_CLI = D1.NO_CLI` `AND d2.CP_CLI LIKE '75%'` `ORDER BY d2.NO_CLI`
`6.1`	`QBE`	`Query` `commande.DB \| NO_CLI \| DATE_COM \|` `ENSEMBLE \| _nocli \| @@/01/@@@@ \|` `Client.db \| NO_CLI \| NOM_CLI \| PRENOM_CLI \| CP_CLI \|` `\| aucun _nocli \| Check \| Check \| 92.. \|` `EndQuery`
	`SQL`	`SELECT DISTINCT C.NO_CLI` `FROM Client.db C, commande.db D` `WHERE c.CP_CLI LIKE "92%"` `AND c.NO_CLI = d.NO_CLI` `AND c.NO_CLI not in (SELECT NO_CLI` `FROM commande` `WHERE EXTRACT(MONTH FROM DATE_COM) = 1))`
`6.2`	`QBE`	`Query` `Client.db \| NO_CLI \| CP_CLI \|` `\| _jointure1 \| 13@@@ \|` `commande.DB \| NO_CLI \| NO_COM \| DATE_COM \| MONTANT_HT_COM \|` `\| _jointure1 \| Check _jointure2 \| @@/02/1998 \| _MHT \|` `commande.DB \| MONTANT_TAXES_COM \|` `\| _MT, calc _MHT+_MT as TOTAL \|` `lignProd.DB \| NO_COM \| REF_PROD \| FABRIQUANT_PROD \|` `\| _jointure2 \| count <=1 \| CANON \|` `EndQuery`
	`SQL`	`SELECT DISTINCT D1.NO_COM,` `D1.MONTANT_HT_COM + D1.MONTANT_TAXES_COM AS TOTAL` `FROM Client.db D0, commande.DB D1` `WHERE D1.NO_CLI = D0.NO_CLI` `AND D0.CP_CLI like '13%'` `AND D1.DATE_COM >= '02/01/1998'` `AND D1.DATE_COM < '03/01/1998'` `AND D1.NO_COM IN (SELECT NO_COM` `FROM LignProd` `WHERE FABRIQUANT_PROD = 'CANON'` `GROUP BY NO_COM` `HAVING count(REF_PROD) <= 1)`