Petit benchmark sans concession (MySQL, PostGreSQL, MS SQL Server) - MySQL jusqu'à 6000 fois plus lent !

[SQLpro]

Bonjour,

Pour information, ce petit benchmark concernant un besoin d'extraction de données.

La question est simple... Partant d'une table de période de temps, agréger les périodes recouvrantes par item, pour n'en avoir plus que l'essentiel... (packing d'intervalles).

Tests effectuées sur MySQL version 5.5, PostGreSQL version 8.4 et MS SQL Server version 2008 R2 avec des jeux d'essais allant de 1 000 à 3 000 000 de lignes.
Résultats :

• PotsGreSQL est entre 3 et 13 fois moins rapide que SQL Server
• MySQL est entre 59 et 5 947 fois moins rapide que SQL Server

...en fonction de la montée en charge.

Bref, une excellente surprise pour PostGreSQL qui confirme grandes fonctionnalités, qualités et performances dans le monde des SGBDR libre !

Tous les détails, les conditions et les scripts SQL pour rejouer ce benchmark chez vous sur : http://blog.developpez.com/sqlpro/p9...lles-en-sql-1/

A +

[Chtulus]

Bonsoir,

Excellent Frédéric comme d'hab, merci ! sympa ce test

[iberserk]

Oui une bonne 'surprise' qui prouve aussi qu'on en a pour son argent avec SQL SERVER...ce que tout le monde n'a pas forcément intégré...

[iberserk]

SQL PRO-> Décidément à fond dans les intervalles en ce moment!

[lerieure]

Il en existe un entre Oracle et SQL Server ?

[SQLpro]

Non je l'ai pas fait. Si quelqu'un à la bonté d'âme de traduire les procédures SQL Server ou PG en Oracle, je ferais les tests en addemdum.

A +

[dehorter olivier]

bonjour

et pour FIREBIRD ?

bien que la version 2.5 n'inclue pas encore les fonctions de fenêtrage qui arrivent dans la version 3 (donc sol 1 et 2 pour le moment)


je veux bien voir si des modifications sont a faire dans les requêtes ?

a+
olivier

[SQLpro]

idem... Traduisez les requêtes et procédures et je me ferais un plaisir de rouler cela à titre de test !

A +

[dehorter olivier]

bonjour et MERCI

voici le scprit pour FIREBIRD 2.5

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
SET SQL DIALECT 3;
 
SET NAMES ISO8859_1;
 
CREATE DATABASE 'C:\Users\EG\Documents\test_sqlpro1.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1 COLLATION ISO8859_1;
 
 
CREATE GENERATOR GEN_T_INTERVAL_ITV_ID;
SET GENERATOR GEN_T_INTERVAL_ITV_ID TO 1001;
 
CREATE TABLE T_INTERVAL_ITV (
    ITV_ID     INTEGER NOT NULL,
    ITV_ITEM   VARCHAR(16) NOT NULL,
    ITV_DEBUT  TIMESTAMP NOT NULL,
    ITV_FIN    TIMESTAMP NOT NULL
);
 
 
ALTER TABLE T_INTERVAL_ITV ADD CONSTRAINT CHK_ITV_FIN_DEBUT CHECK (itv_fin >= itv_debut);
 
ALTER TABLE T_INTERVAL_ITV ADD CONSTRAINT T_INTERVAL_ITV_PKEY PRIMARY KEY (ITV_ID);
 
CREATE INDEX X_ITV_ITM_DEB_FIN ON T_INTERVAL_ITV (ITV_ITEM, ITV_DEBUT, ITV_FIN);
CREATE INDEX X_ITV_ITM_FIN_DEB ON T_INTERVAL_ITV (ITV_ITEM, ITV_FIN, ITV_DEBUT);
 
CREATE TRIGGER T_INTERVAL_ITV_BI FOR T_INTERVAL_ITV
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  IF (new.itv_id IS NULL) then
    new.itv_id = gen_id(gen_t_interval_itv_id,1);
end;
 
 
CREATE PROCEDURE P_GENERATE_ROWS (
    DEBUT_INTERVAL TIMESTAMP,
    NUM_ITEMS INTEGER,
    INTERVALS_PAR_ITEM INTEGER,
    DUREE_MAX_S INTEGER)
AS
  DECLARE VARIABLE Fin_interval TIMESTAMP;
  DECLARE VARIABLE I            INTEGER;
  DECLARE VARIABLE J            INT;
  DECLARE VARIABLE Itm          VARCHAR(16);
  DECLARE VARIABLE D            TIMESTAMP;
  DECLARE VARIABLE F            TIMESTAMP;
  DECLARE VARIABLE Duree        INT;
BEGIN
    Fin_interval = DATEADD(SECOND, :Duree_max_s,:Debut_interval );
    I = 1;
    J = 1;
    WHILE (I <= :Num_items) DO
    BEGIN
        WHILE (J <= :Intervals_par_item) DO
        BEGIN
            D = DATEADD(SECOND, RAND() * :Duree_max_s * 10, :Debut_interval);
            Duree = FLOOR(RAND() * :Duree_max_s);
            F = DATEADD(SECOND, :Duree, :D);
            Itm = 'Item' || I;
            INSERT INTO T_interval_itv (Itv_item,
                                        Itv_debut,
                                        Itv_fin)
              VALUES (:Itm,
                      :D,
                      :F);
            J = :J + 1;
        END
        J = 1;
        I = :I + 1;
    END
END;
 
EXECUTE PROCEDURE P_GENERATE_ROWS ('2001-01-01', 10, 100, 3600);
 
 
-- solution 1 classique 
WITH  
T0 AS  
(SELECT PRE.ITV_ITEM, 
        PRE.ITV_DEBUT AS D1, PRE.ITV_FIN AS F1,   
        DER.ITV_DEBUT AS D2, DER.ITV_FIN AS F2  
 FROM   T_INTERVAL_ITV PRE  
        INNER JOIN T_INTERVAL_ITV DER  
              ON PRE.ITV_DEBUT <= DER.ITV_FIN 
                 AND PRE.ITV_ITEM = DER.ITV_ITEM)  
SELECT DISTINCT ITV_ITEM, D1 AS ITV_DEBUT, F2 AS ITV_FIN 
FROM   T0 AS I  
WHERE  NOT EXISTS (SELECT *   
                   FROM   T_INTERVAL_ITV SI1  
                   WHERE  (SI1.ITV_DEBUT < I.D1   
                           AND I.D1 <= SI1.ITV_FIN 
                           AND I.ITV_ITEM = SI1.ITV_ITEM )  
                      OR  (SI1.ITV_DEBUT <= I.F2   
                           AND I.F2 < SI1.ITV_FIN 
                           AND I.ITV_ITEM = SI1.ITV_ITEM))  
AND NOT EXISTS (SELECT *   
                FROM   T_INTERVAL_ITV SI2 
                WHERE  D1 < SI2.ITV_DEBUT   
                  AND  SI2.ITV_DEBUT <= F2 
                  AND  I.ITV_ITEM = SI2.ITV_ITEM  
                  AND  NOT EXISTS (SELECT *   
                                   FROM   T_INTERVAL_ITV SI3  
                                   WHERE  SI3.ITV_DEBUT < SI2.ITV_DEBUT  
                                     AND  SI2.ITV_DEBUT <= SI3.ITV_FIN 
                                     AND  SI2.ITV_ITEM = SI3.ITV_ITEM )); 
 
 
-- solution 2 SnodGrass 
WITH T  
AS (SELECT F.ITV_DEBUT, L.ITV_FIN, F.ITV_ITEM 
    FROM   T_INTERVAL_ITV AS F  
           JOIN T_INTERVAL_ITV AS L  
                ON F.ITV_FIN <= L.ITV_FIN 
                   AND F.ITV_ITEM = L.ITV_ITEM 
           INNER JOIN T_INTERVAL_ITV AS E       
                 ON F.ITV_ITEM = E.ITV_ITEM  
    GROUP  BY F.ITV_DEBUT, L.ITV_FIN,  F.ITV_ITEM  
    HAVING COUNT(CASE  
                    WHEN (E.ITV_DEBUT < F.ITV_DEBUT AND F.ITV_DEBUT <= E.ITV_FIN)  
                          OR (E.ITV_DEBUT <= L.ITV_FIN AND L.ITV_FIN < E.ITV_FIN) 
                    THEN 1   
                 END) = 0)  
SELECT ITV_ITEM, ITV_DEBUT, MIN(ITV_FIN) AS ITV_FIN  
FROM   T   
GROUP  BY ITV_ITEM, ITV_DEBUT;

[philwood]

Bonjour, ce test me laisse dubitatif.

Les paramètres de chaque SGBD ont été laissés par défaut et il faut quand même savoir que Postgresql n'est configuré par défaut qu'avec seulement 32 MB en shared_buffers alors que Sql Server 2000 MB !!

Ca fausse énormément les résultats à mon avis ...

Cdt

[ced]

Je suis bien d'accord... J'ai fait la même remarque sur le blog.
Les paramétrages par défaut des 3 SGBD sont trop différents pour que le test ait une quelconque signification en l'état, à mon avis.

[pacmann]

Salut !

Sous Oracle, il doit y avoir de la très bonne solution à la base de LEAD / LAG

EDIT : chuis un peu con : ça veut dire quoi "petit benchmark sans concession" ?

[SQLpro]

Envoyé par philwood

Les paramètres de chaque SGBD ont été laissés par défaut et il faut quand même savoir que Postgresql n'est configuré par défaut qu'avec seulement 32 MB en shared_buffers alors que Sql Server 2000 MB !!

Vous avez sans doute raison sur le long terme, mais le volume des données est sommes toute assez faible et la plupart des installations se font à défaut. Rare sont les installations à être tunée avant même la mise en production.

Pour rappel, la table avec 10 000 lignes dedans, fait moins de 400 Ko...
Donc, avec 32 MB, vous traitez 800 000 lignes en mémoire avec PG !
Or la différence se voit bien avant... Votre argument tombe donc à l'eau !

A +