Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Dans l'article complet il y a plus de texte… (il fait déjà 90 pages) et je n'ai pas trouvé le moyen de ne pas avoir de requête récrite pour l'insensibilité à la casse.
Donc soit tu ne récrit pas la requête, mais ça fait pas le job, SOIT IL FAUDRA RECRITE TOUTES LES REQUÊTES SANS EXCEPTION, (les bases SQL Server étant généralement créées en CI)…
J'ai donc présenté une requête avec LOWER puisqu'il n'existe pas de solution sans récriture.
En sus l'index sur fonction du genre :
Résout bien le problème de performances, mais oblige toujours à récrire la requête !
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE INDEX X ON Table (LOWER(Colonne))
Quand à la seconde requête qui est 300 000 fois moins rapide, celle avec les accents et autres caractères diacritique, je constate que personne ici, ni même Sodium, n'a trouvé une solution pour un temps de réponse raisonnable !
A +
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Oui tu dis une connerie, puisque :
- PostgreSQL tourne aussi sous Windows, et a donc rigoureusement la même problématique sous cet OS
- SQL Server tourne aussi sous Linux, et n'a donc rigoureusement pas la même problématique sous cet OS
- Dans tous les cas, pour la plupart des opérations de maintenance, PostgreSQL impose une coupure de la base de données, alors que SQL Server ne l'impose pas
- Que dans n'importe quelle boîte utilisant PostgreSQL les coupures liées à ces tâches se produisent bien plus souvent que celles liées à une mise à jour sous Windows
CQFD
On ne jouit bien que de ce qu’on partage.
Je rajouterais même que PostgreSQL n'a aucun moyen de ne pas imposer une coupure de service de données puisqu'il est toujours incapable de faire une basculement synchrone automatique et sans coupure…. Ce que fais SQL Server depuis la version 2005 avec le mirroring et depuis la version 2012 avec AlwaysOn !
A +
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Pourtant j'ai déjà donné un moyen: remplacer TEXT ou VARCHAR par CITEXT dans la déclaration de la table. L'effet est de rajouter implicitement des LOWER partout où on fait des comparaisons, y compris dans la création de l'index.
Note: ne pas oublier d'exécuter d'abord "create extension citext;" sinon le type n'est pas défini.
Leur nom est peut-être un peu moins explicite (basé sur CDLR) mais explication ci-après:
Avec PostgreSQL 12, je viens de faire le test:
et la requête avec DATUM = 'méli-mélo' trouve bien les variantes sans accents.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 CREATE COLLATION ignore_accents (provider = icu, locale = 'fr-u-ks-level1-kc-false', deterministic=false); CREATE TABLE T_ACCENTS (ID SERIAL PRIMARY KEY, DATUM VARCHAR(256) COLLATE ignore_accents);
Voila, comme je n'ai pas le même ordinateur que toi, je te laisse tester dans les mêmes conditions.
Au passage tu noteras que ignore_accents inclut aussi l'insensibilité à la casse (sinon écrire kc-true), donc c'est aussi une bonne alternative au vieux module citext ... mais ça ne marche qu'avec PostgreSQL 12.
Voila qui est fait juste au dessus.
Bonjour,
Il suffi de surcharger l'opérateur de comparaison pour les types concernés.
Par exemple pour le VARCHAR :
Et là... plus besoin de réécrire les requêtes
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 CREATE FUNCTION VARCHAR_EQUALITY_CI(VARCHAR, VARCHAR) RETURNS BOOLEAN AS 'select LOWER($1) = LOWER($2);' LANGUAGE SQL RETURNS NULL ON NULL INPUT; CREATE OPERATOR = (LEFTARG = VARCHAR, RIGHTARG = VARCHAR, PROCEDURE = VARCHAR_EQUALITY_CI);
Un problème se posera alors pour les requêtes où on veut justement une comparaison sensible à la casse, comme les vérifications de mot de passe. Mais elles devraient être bien nombreuses....
en jouant avec les types, on devrait même pouvoir s'en sortir sans réécrire une seule requete
C'est rigolo car tu démontres assez bien ce que je disais sur un topic précédent : Sql est beaucoup trop verbeux, n'a aucune couche d'abstraction il est impossible de faire du code application propre en recourant uniquement à lui. Donc oué du coup tu te retrouves à réécrire des centaines de requêtes au lieu de réécrire une seule fonction qui fait la communication entre le programme et la bdd.
Et je ne parle même pas des alias à la con (SELECT a.id, c.title, d.quechoche) que les codeurs mettent parce que le langage est trop verbeux et qui rendent les requêtes illisibles pour les autres.
Bref :
Prenez n'importe quelle requete SQL, et faites l'équivalent dans le langage procédural de votre choix... je suis pret à parier sur lequel des deux langages sera le plus concis...
Justement si, les vues notamment.
Mais vous ne voulez pas en entendre parler - probablement car elles sont mal prises en charge par votre ORM favori ?! -
Au menu, il y a aussi les procédures stockées.
Mais il se trouve que la plupart des développeurs, par flemme et/ou méconnaissance, écrivent les requêtes directement sur les tables. en effet ça réduit beaucoup la marge de manœuvre lorsqu'il faut apporter des modifications.
Mais il faut comparer ce qui est comparable : Quel serait le niveau de maintenabilité d'un programme qui serait écrit d'un bloc (genre dans le main), sans recourt à aucune classe ni aucune fonction ?
Là encore, il faut comparer ce qui est comparable : on parle ici de changer de SGBDR...
à votre avis, combien de lignes de code faudrait-il réécrire si on voulait passer de .Net à Java ?
Encore une fois il ne s'agit pas de cette requête, mais de celle-ci :
Je recopie :
[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 :
--> à ce stade nous avons 10 192 056 lignes dans la table
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12 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
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 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 msSQL 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 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 :
Code :
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 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 msBilan : 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 CREATE INDEX X_ACCENTS ON T_ACCENTS (DATUM); EXPLAIN ANALYZE SELECT * FROM T_ACCENTS WHERE remove_fr_accents(DATUM) = 'meli-melo'; --> 284 990 ms
A +
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Déjà fait, exemple.
La plupart des langages procéduraux ont un opérateur "ou court circuit", notons-le || comme dans les langages dérivés du C ou plutôt OR ELSE comme dans le langage Ada. Donc, A OR ELSE B signifie A ou B, mais en sachant qu'on évalue B seulement si A est vrai (parce que sinon, on sait très bien que la condition sera fausse).
Maintenant, dans mon travail j'ai une requête qui revient souvent: trouver toutes les lignes contenant une certaine phrase, ou à défaut une phrase approchante. Autrement dit, on ne fait la recherche approchante que si on n'a rien trouvé avec la phrase exacte.
Naturellement je voudrais écrire quelque chose du style(l'opérateur % est défini dans le module pg_trgm de Postgres). Mais AVANT DE ME HURLER DESSUS POUR DIRE QUE ÇA NE MARCHE PAS, oui je sais, point d'opérateur OR ELSE à l'horizon dans SQL. Evidemment les "pros" SQL vont nous expliquer que cette requête est du niveau d'un élève qui a fait deux semaines de SQL, et donc nous pondre ceci
Code : Sélectionner tout - Visualiser dans une fenêtre à part select * from myTable where texte = :phrase OR ELSE texte % :phrase
Désolé, je sais que cette requête fonctionne mais dans le genre concis, je crois qu'on a la réponse. En plus cette requête contient plusieurs fois le même texte (select * from DEMO) ce qui est une source potentielle de fautes de frappe si on écrit ses requêtes soi-même. Et encore, on est sur un "select *", si on avait énuméré les lignes, le risque d'erreur dû à la répétition est encore plus grand.
Évidemment un moyen d'éliminer le problème est de générer les requêtes, mais il paraît que c'est pas bien...
ET DONC, LA COLLATION ignore_accents ELLE FAIT QUOI D'APRÈS TOI???
Elle permet d'ignorer les accents, de sorte que where datum='meli-melo' donne les mêmes réponses que where datum='méli-mélo' (au cas où tu veuilles faire semblant de me reprocher d'avoir laissé l'accent dans le message précédent...)
Évidemment que les autres signes diacritiques sont compris. Et le paramètre après "kc-" permet de définir si on garde la sensibilité à la casse ou pas.
Il suffit de créer un nouveau type dérivé de TEXT ou de VARCHAR, et de ne redéfinir l'opérateur que sur ce type. Tu peux alors faire cohabiter des colonnes TEXT et CITEXT, même dans la même table. C'est ce que fait le module CITEXT, en fait...
Elle ne fait pas son boulot…..
Démonstration avec la requête de test :
La phrase test :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 CREATE COLLATION ignore_accents (provider = icu, locale = 'fr-u-ks-level1-kc-false', deterministic=false); CREATE TABLE T_ACCENTS2 (ID SERIAL PRIMARY KEY, DATUM VARCHAR(256) COLLATE ignore_accents);
Code : Sélectionner tout - Visualiser dans une fenêtre à part INSERT INTO T_ACCENTS2 (DATUM) VALUES ('Écurée par lAÿ Lætitia et son garçon arrivèrent à Paris');Ne retourne aucune donnée !
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT * FROM T_ACCENTS2 WHERE DATUM = 'Ecoeuree par lAy Laetitia et son garcon arriverent a Paris'
Donc je confirme bien que PostgreSQL est incapable d'effectuer des recherches sans tenir compte des caractères diacritiques (accents, cédille, ligature…)
Je te cite :
Mentirais tu ?
A +
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Attention, dire qu'il ment c'est de la diffamation, processus juridique, tout ça tout ça
il faut utiliser unaccent
https://www.postgresql.org/docs/12/u...id-1.11.7.52.7
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
* * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager