Dans le cadre d'un long article à paraître dont le sujet est l'étude de la portabilité de bases Microsoft SQL Server vers PostGreSQL, j'ai été amené à effectuer des tests comparatifs.
Je publie ici un exemple concernant d'importantes différences de performances concernant les manipulation de chaines de caractères et notamment dans le cadre de recherches avec insensibilité à la casse ou aux accents.
ATTENTION : dans certains cas il existe des solutions de contournement, mais elles imposent une restructuration des tables (notamment l'ajout de colonnes) et la récriture des requêtes.
Dans tous les cas, ces solutions ont un coût extrémement important, qui, en pratique pourront être un frein, voir un arrêt de mort concernant un tel projet de portage...
Dans un projet portage on essaye de minimiser l'impact de modification, sinon, le portage risque de coûter plus cher que l'économie que l'on tente de réaliser...
Voici les tests que j'ai mené sur un portable HP doté comme suit :
- CPU Core i7 6500U cadencés à 2,5 Ghz (4 coeurs)
- RAM 32 Go
- Disques SSD
Avant de publier cette série d'article, je vais faire des tests complémentaires sur une machine plus costaude (48 coeurs, 128 Go de RAM, disques SAS et SSD)
* * * * * * *
[T-0050] Test mettant en évidence les contre-performances de PostGreSQL pour des recherches insensibles à la casse
Test pour SQL Server :
Sans index la recherche a mis 1 857 ms, avec index 1 ms. SQL Server effectuant une recherche dans l’index.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 CREATE TABLE T_CASSE (ID INT IDENTITY PRIMARY KEY, DATUM VARCHAR(256) COLLATE French_CI_AS); GO INSERT INTO T_CASSE VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); GO 7 --> permet de lancer la requête 7 fois INSERT INTO T_CASSE SELECT T1.DATUM FROM T_CASSE AS T1 CROSS JOIN T_CASSE AS T2; GO 3 --> permet de lancer la requête 3 fois --> à ce stade nous avons 10 192 056 lignes dans la table INSERT INTO T_CASSE VALUES ('Méli-Mélo'); GO SET STATISTICS TIME ON; GO SELECT * FROM T_CASSE WHERE DATUM = 'méli-mélo'; --> Temps UC = 3109 ms, temps écoulé = 1857 ms. CREATE INDEX X_CASSE ON T_CASSE (DATUM); GO SELECT * FROM T_CASSE WHERE DATUM = 'méli-mélo'; --> Temps UC = 0 ms, temps écoulé = 1 ms.
Test pour PostGreSQL :
Sans index, et dans le meilleur des cas PostGreSQL s’en tire un peu mieux que SQL Server grâce à l’opérateur ILIKE, mais sans cet opérateur il est 2,5 fois plus lent. À noter que les plans d’exécution montrent l’emploi du parallélisme avec de 2 threads et systématiquement un parcours séquentiel. Avec un index, le ILIKE est même légèrement moins bon que sans (nous avons réitérer la requête pour être sûr de nous...). Dans tous les cas, avec un index, PostGreSQL est battu à plate couture !
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 CREATE TABLE T_CASSE (ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, DATUM VARCHAR(256)); INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !'); --> lancez 3 fois cette requête : INSERT INTO T_CASSE (DATUM) SELECT T1.DATUM FROM T_CASSE AS T1 CROSS JOIN T_CASSE AS T2; --> à ce stade nous avons 10 192 056 lignes dans la table INSERT INTO T_CASSE (DATUM) VALUES ('Méli-Mélo'); EXPLAIN ANALYZE SELECT * FROM T_CASSE WHERE LOWER(DATUM) = 'méli-mélo'; --> Execution time : 4795.370 ms EXPLAIN ANALYZE SELECT * FROM T_CASSE WHERE DATUM ILIKE 'méli-mélo'; --> Execution time : 1541.112 ms EXPLAIN ANALYZE SELECT * FROM T_CASSE WHERE DATUM ~ 'méli-mélo'; --> Execution time : 11910.661 ms CREATE INDEX X_CASSE ON T_CASSE (DATUM); EXPLAIN ANALYZE SELECT * FROM T_CASSE WHERE LOWER(DATUM) = 'méli-mélo'; --> Execution time : 5197.753 ms EXPLAIN ANALYZE SELECT * FROM T_CASSE WHERE DATUM ILIKE 'méli-mélo'; --> Execution time : 1718.089 EXPLAIN ANALYZE SELECT * FROM T_CASSE WHERE DATUM ~ 'méli-mélo'; --> Execution time : 10905.050 ms
Bilan : SQL Server est 1 718 fois plus rapide que PostGreSQL sur cette recherche insensible à la casse...
* * * * * * *
[T-0060] Test mettant en évidence les contre-performances de PostGreSQL pour des recherches insensibles aux accents et autres caractères diacritiques
Script pour SQL Server :
SQL Server a mis 1 805 ms sans index et 1 ms avec index, grâce à la collation.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 CREATE TABLE T_ACCENTS (ID INT IDENTITY PRIMARY KEY, DATUM VARCHAR(256) COLLATE French_CS_AI); GO INSERT INTO T_ACCENTS VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); GO 7 --> ceci exécute la requête 7 fois INSERT INTO T_ACCENTS SELECT T1.DATUM FROM T_ACCENTS AS T1 CROSS JOIN T_ACCENTS AS T2; GO 3 --> ceci exécute la requête trois fois --> à ce stade nous avons 10 192 056 lignes dans la table INSERT INTO T_ACCENTS VALUES ('méli-mélo'); SET STATISTICS TIME ON; SELECT * FROM T_ACCENTS WHERE DATUM = 'meli-melo' --> Temps UC = 3126 ms, temps écoulé = 1805 ms CREATE INDEX X_ACCENTS ON T_ACCENTS (DATUM); GO SELECT * FROM T_ACCENTS WHERE DATUM = 'meli-melo' --> Temps UC = 0 ms, temps écoulé = 1 ms.
Script PostGreSQL :
Bilan : SQL Server est en moyenne 300 000 fois plus rapide que PostGreSQL sur cette recherche insensible aux accents...
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 CREATE TABLE T_ACCENTS (ID SERIAL PRIMARY KEY, DATUM VARCHAR(256)); INSERT INTO T_ACCENTS (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); INSERT INTO T_ACCENTS (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); INSERT INTO T_ACCENTS (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); INSERT INTO T_ACCENTS (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); INSERT INTO T_ACCENTS (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); INSERT INTO T_ACCENTS (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); INSERT INTO T_ACCENTS (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); --> à jouer 3 fois : INSERT INTO T_ACCENTS (DATUM) SELECT T1.DATUM FROM T_ACCENTS AS T1 CROSS JOIN T_ACCENTS AS T2; --> à ce stade nous avons 10 192 056 lignes dans la table INSERT INTO T_ACCENTS (DATUM) VALUES ('méli-mélo'); --> création de la fonction de désaccentuation : CREATE OR REPLACE FUNCTION remove_fr_accents(string text) RETURNS text AS $$ DECLARE out_str text; BEGIN SELECT translate(string, 'âäàÁÂÄèéêëÈÉÊËîïÎÏôöÕÖùûüÙÛÜÿÇç', 'aaaAAAeeeeEEEEiiIIooOOuuuUUUyYCc') INTO out_str; SELECT replace(out_str, '', 'OE') INTO out_str; SELECT replace(out_str, 'Æ', 'AE') INTO out_str; SELECT replace(out_str, 'æ', 'ae') INTO out_str; SELECT replace(out_str, '', 'oe') INTO out_str; RETURN out_str; END; $$ LANGUAGE plpgsql; EXPLAIN ANALYZE SELECT * FROM T_ACCENTS WHERE remove_fr_accents(DATUM) = 'meli-melo'; --> 331 666 ms CREATE INDEX X_ACCENTS ON T_ACCENTS (DATUM); EXPLAIN ANALYZE SELECT * FROM T_ACCENTS WHERE remove_fr_accents(DATUM) = 'meli-melo'; --> 284 990 ms
Comme la plupart du temps, les bases Microsoft SQL Server sont créées avec une collation sensible aux caractères diacritiques, nous allons maintenant procéder à un nouveau test. La table sera créée avec une collation sensible à la casse et nous allons utiliser l’opérateur COLLATE, pour retrouver notre information...
Aussi surprenant que cela puisse paraître SQL Server a mis 1 ms pour retrouver l’information sans que la table soit indexée !
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 CREATE TABLE T_ACCENTS2 (ID INT IDENTITY PRIMARY KEY, DATUM VARCHAR(256) COLLATE French_CI_AS); GO INSERT INTO T_ACCENTS2 VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris'); GO 7 --> ceci exécute la requête 7 fois INSERT INTO T_ACCENTS2 SELECT T1.DATUM FROM T_ACCENTS2 AS T1 CROSS JOIN T_ACCENTS2 AS T2; GO 3 --> ceci exécute la requête trois fois --> à ce stade nous avons 10 192 056 lignes dans la table SELECT COUNT(*) FROM T_ACCENTS2 INSERT INTO T_ACCENTS2 VALUES ('méli-mélo'); SET STATISTICS TIME ON; SELECT * FROM T_ACCENTS2 WHERE DATUM COLLATE French_CI_AI = 'meli-melo' --> Temps UC = 0 ms, temps écoulé = 1 ms
Bilan : une nouvelle fois SQL Server est 300 000 fois plus rapide que PostGreSQL sur cette recherche insensible aux accents et sans faire usage d’index dans SQL Server !...
* * * * * *
Si quelqu'un à des idées pour améliorer les performances de PostgreSQL je suis preneur. Bien entendu pour le second test on peut utiliser le module unaccent, mais cela modifie la structure de la table et oblige de réécrire les requêtes…
A +
Partager