Discussion :

[SQLpro]

bien essayé... mais il y a déjà quelqu'un qui a modifié la requête... et donné des résultats... certain bien que tu suives un minimum

soeur anne, devrait peut-être pensé à demander un chien guide

C'est vous qui êtes aveugle, induit pas "l'imposteur" j'en convient, qui confond les deux requêtes….

A +

[SQLpro]

J'attends une réponse un peu argumentée parce que évidemment le cluster je connais on en fait depuis 20 ans, mais c'est plus le même tarif non plus.

Licence, matériel, même en VM hein...

Pour info, Windows standard c'est 800 €. SQL Server en secondaire passif c'est 0 €. Microsoft n'a jamais fait payé les serveurs de secours MS SQL Server…. Il serait temps d'arrêter de dire des conneries !

A +

[SQLpro]

Rien que le titre de l'article est mensonger de toute façon. Quand on écrit un article, le minimum est que son titre de informe sur ce que celui-ci contient. Ce n'est clairement pas le cas ici et oui, c'est clairement malhonnête.

Vous avez pas fini de mentir et de traiter les gens de malhonnête sans jamais rien démontrer ?
Ne serait pas vous qui avez aucune honnêteté intellectuelle ?

[SQLpro]

Note : les collations non sensibles à la casse sont arrivées avec la version 12 de postgres.
https://www.postgresql.org/docs/12/c...NDETERMINISTIC

Merci de me donner un exemple… J'ai tenté mais ça ne fonctionne pas !

A +

[SQLpro]

…
Trop drôle, donc en fait entre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM   T_CASSE WHERE  DATUM = 'méli-mélo';

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM   T_CASSE WHERE   LOWER(DATUM) = 'méli-mélo';;

il n'y a aucune ré-écriture de requête? Par contre, rajouter LOWER dans la création de l'index, ça, c'est trop de travail?

Merci beaucoup, j'étais resté à Postgres 10 mais voila une fonction qui m'intéresse bien. Surtout que dans mon message précédent je suggérais justement l'utilisation de ICU...

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 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX X ON Table  (LOWER(Colonne))

Résout bien le problème de performances, mais oblige toujours à récrire la requête !

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 +

[StringBuilder]

Je vais surement dire une connerie mais en même temps j'ai envie d'apprendre : comment on fait du 24/7 avec windows et sql server, parce que moi je ne sais pas faire, et c'est très sérieux

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

[SQLpro]

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

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 +

[esperanto]

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.

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.

j'ai jeté un oeil à la doc de postgresql pour cette version 12 : je ne crois pas que des options du type CI ou AI y soient. En tout cas je vois plein de locales dans la collections de collations, mais pas d'options de ce type.

Leur nom est peut-être un peu moins explicite (basé sur CDLR) mais explication ci-après:

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 !

Avec PostgreSQL 12, je viens de faire le 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_ACCENTS (ID      SERIAL PRIMARY KEY,
                        DATUM   VARCHAR(256) COLLATE ignore_accents);

et la requête avec DATUM = 'méli-mélo' trouve bien les variantes sans 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.

Merci de me donner un exemple… J'ai tenté mais ça ne fonctionne pas !

Voila qui est fait juste au dessus.

[aieeeuuuuu]

Bonjour,

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.

Il suffi de surcharger l'opérateur de comparaison pour les types concernés.

Par exemple pour le VARCHAR :

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);

Et là... plus besoin de réécrire les requêtes

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

[Sodium]

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.

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 :

[aieeeuuuuu]

Sql est beaucoup trop verbeux

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...

SQL [...] n'a aucune couche d'abstraction

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 ?

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.

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 ?

[SQLpro]

...
Avec PostgreSQL 12, je viens de faire le 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_ACCENTS (ID      SERIAL PRIMARY KEY,
                        DATUM   VARCHAR(256) COLLATE ignore_accents);

et la requête avec DATUM = 'méli-mélo' trouve bien les variantes sans accents..

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 :

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 ('Écœurée par l’Aÿ 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

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 ms

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.

SQL Server a mis 1 805 ms sans index et 1 ms avec index, grâce à la collation.

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 ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ 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

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

Bilan : SQL Server est en moyenne 300 000 fois plus rapide que PostGreSQL sur cette recherche insensible aux accents...


A +

[SQLpro]

Bonjour,



Il suffi de surcharger l'opérateur de comparaison pour les types concernés.

Par exemple pour le VARCHAR :

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);

Et là... plus besoin de réécrire les requêtes

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

Ben non, parce que dans la base tu peux avoir des colonnes avec une collation CI et d'autres avec une collation CS…. Si tu surcharges l'opérateur tu imposes une méthode de comparaison toujours CI, ce qui n'est pas le but….

A +

[esperanto]

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...

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from myTable where texte = :phrase OR ELSE texte % :phrase

(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

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...

[esperanto]

Encore une fois il ne s'agit pas de cette requête, mais de celle-ci :

[T-0060] Test mettant en évidence les contre-performances de PostGreSQL pour des recherches insensibles aux accents et autres caractères diacritiques

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.

Ben non, parce que dans la base tu peux avoir des colonnes avec une collation CI et d'autres avec une collation CS…. Si tu surcharges l'opérateur tu imposes une méthode de comparaison toujours CI, ce qui n'est pas le but….

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...

[Sodium]

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...

Pourquoi procédural ? L'intéret d'un ORM, c'est justement de ne pas être procédural et de centraliser au maximum la génération de requêtes afin de ne modifier que cette couche en cas de modification de la db ou des requêtes.

[SQLpro]

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 :

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);

La phrase test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO T_ACCENTS2 (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM T_ACCENTS2 WHERE DATUM = 'Ecoeuree par l’Ay Laetitia et son garcon arriverent a Paris'

Ne retourne aucune donnée !

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 :

Évidemment que les autres signes diacritiques sont compris.

Mentirais tu ?

A +

[Sodium]

Attention, dire qu'il ment c'est de la diffamation, processus juridique, tout ça tout ça

[marc.collin]

il faut utiliser unaccent

https://www.postgresql.org/docs/12/u...id-1.11.7.52.7

[SQLpro]

il faut utiliser unaccent

https://www.postgresql.org/docs/12/u...id-1.11.7.52.7

Et donc à nouveau récrire toutes les requêtes….

A +