[ASE]UPPER sur index

[NicaeaCivitas]

Bonjour,

Lorsque l'on fait un upper sur une colonne sur lequel il y a un index, j'ai cru comprendre que la valeur est recalculée, de ce fait l'optimisation sur l'index ne se fait plus.
Y a t'il une manière d'utiliser de manière optimale un index case sensitive ?

Par exemple ici (index sur Nom):

Code :

1
2
3
 
SELECT USID FROM Employes
WHERE upper(Nom) NOT LIKE 'Test%'

[Jean.Cri1]

Avec nom not like '[Tt][Ee][Ss][Tt]%'
l'index devrait etre utilisé mais il faut voir le cout du like ( je n'en sais trop rien)
j'attends le résultat de tes tests et les réactions d'autrui.

PS: upper (nom) sera plutot like 'TEST%'

[NicaeaCivitas]

Merci à toi, je vais tester ça.

Pour ton Post-Scriptum, bien sûr, j'ai recopié trop vite.

[mpeppler]

Envoyé par NicaeaCivitas

Lorsque l'on fait un upper sur une colonne sur lequel il y a un index, j'ai cru comprendre que la valeur est recalculée, de ce fait l'optimisation sur l'index ne se fait plus.

Effectivement, si on applique une fonction à une colonne indexée celui-ci n'est plus utilitable par l'optimiseur (sauf en 15.0 où on peut créer une index directement sur la fonction).

Il y a quelques solutions possibles:

1. Utiliser un LIKE comme suggèré par Jean.Cri1

2. Créer une colonne avec les données en UPPER, et indexer cette colonne

3. Mettre le serveur en mode "case insensitive".

La 3eme solution est la plus complète, et peut-être très utile, mais attention - elle s'applique au serveur en entier, y compris tous les noms de procédures, colonnes, tables, etc.

Michael

[Jean.Cri1]

Personnellement j'ai essayé ma proposition et cela n'a PAS MARCHé !!!
Mon showplan ne se sert de l'index que lorsque le debut du like est fixe ( pas de choix [] ).

Désolé ...
Si quelqu'un a d'autres idées ?

[fadace]

Non, effectivement, les expressions régulières sont un os... et ASE n'autorise pas encore d'index sur fonction

Dans ce cas précis, une des options et effectivement d'ajouter une colonne équivalente à l'UPPER et la renseigner via trigger... ou faire la requête via une SP qui utilisera une table intermédiaire... mais cela équivaudra au scan initial que votre requête fait. N'y a-t-il pas d'aures indexes utilisables dans votre requête complète ?

[NicaeaCivitas]

J'ai essayé la solution de Jean.Cri1. J'ai un gain de 0 à 24% sur le temps d'exécution. Par contre il est vrai que les plans d'exécution sont identiques. Est-ce normal que 2 plans d'exécution identiques ne génére pas le même temps d'exécution ?

En revanche il y a un truc bizarre sur le show plan de la commande suivante :

Code :

1
2
3
 
SELECT USID FROM Employes
WHERE Nom LIKE 'TEST%'

Alors que pour les upper ou les [], (avec LIKE et non NOT LIKE) le show plan m'indique "Positioning at index start", là il m'indique "Positioning by key, Keys are: Nom ASC", alors que Nom n'est pas une clé, juste un index.
Par contre, avec NOT LIKE, le plan d'exécution est le même dans les 3 cas... Comment cela se fait-il ?

[mpeppler]

Envoyé par fadace

et ASE n'autorise pas encore d'index sur fonction

En fait, ces indexes sur fonctions sont possibles en 15.0...

Mais il n'y a pas (encore) beaucoups de sites qui utilisent la 15!

Michael

[mpeppler]

Envoyé par NicaeaCivitas

J'ai essayé la solution de Jean.Cri1. J'ai un gain de 0 à 24% sur le temps d'exécution. Par contre il est vrai que les plans d'exécution sont identiques. Est-ce normal que 2 plans d'exécution identiques ne génére pas le même temps d'exécution ?

Il faut voir le nombre d'IOs, et en particulier le nombre d'IOs physiques.

Alors que pour les upper ou les [], (avec LIKE et non NOT LIKE) le show plan m'indique "Positioning at index start", là il m'indique "Positioning by key, Keys are: Nom ASC", alors que Nom n'est pas une clé, juste un index.
Par contre, avec NOT LIKE, le plan d'exécution est le même dans les 3 cas... Comment cela se fait-il ?

"Positioning by key" veut simplement dire que l'optimiseur va faire un "index scan" plutôt qu'un table scan. L'index scan peut-être plus rapide dans certaines circonstances.

Michael

[NicaeaCivitas]

Envoyé par mpeppler

Il y a quelques solutions possibles:

2. Créer une colonne avec les données en UPPER, et indexer cette colonne

3. Mettre le serveur en mode "case insensitive".


Michael

Je ne connais pas la commande à passer pour le point 2, quelle est-elle ?
Pour le point 3, j'utilise la version ASE 11.9.2.3, le mode case insensitive est-il supporté ?

Merci.

[mpeppler]

Pour le point 2, il faut ajouter une nouvelle colonne (p.ex. Nom_Maj), et créer un trigger qui maintient cette colonne à jour.

P.ex. (code non testé!)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
ALTER TABLE Employes ADD Nom_Maj varchar(30) NULL
go
CREATE TRIGGER Employes_tr ON Employes FOR INSERT, UPDATE
AS
IF UPDATE(Nom)
begin
    UPDATE Employes
         SET Nom_Maj = upper(i.Nom)
       FROM Employes e, inserted i
      WHERE i.pk = e.pk   -- Remplacer "pk" par la PK de la table Employes.
end
go

Pour l'option 3, oui on peut mettre la 11.9.2 en mode "case insensitive". Cela se fait via le "sort order" de l'instance. Il faut utiliser l'outil "sqlloc" pour faire cette modification. Ceci n'est pas quelque chose à faire à la légère: l'impact peut être significatif puisque cela affecte tout le serveur, et autant les noms d'objets (tables, colonnes, etc) que le contenu des tables.

Michael

[fadace]

Envoyé par mpeppler

En fait, ces indexes sur fonctions sont possibles en 15.0...

Mais il n'y a pas (encore) beaucoups de sites qui utilisent la 15!

Michael

Oups ! Bêta-testeur de la 15, je n'ai même pas vu ça ! La honte !