Discussion :

[Shkrups]

Bonjour à tous,

Le logiciel sur lequel je travaille (Apriso) a un type de grille où l'on peut déterminer la requête qui va être exécuté.
Il est possible de filtrer les résultats sur une colonne ce qui a pour effet de re-exécuter la requête après lui avoir ajouté le prédicat correspondant au filtre.

Par exemple, on a cette requête :

Code :

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

1
2
3
4
SELECT *
FROM INVENTORY2_HISTORY_HEADER I2HH
JOIN INVENTORY2_HISTORY_DETAIL I2HD
ON I2HH.ID = I2HD.InventoryHistoryHeaderID

(J'ai simplifié le SELECT mais en réalité la requête récupère quasiment tous les champs)

On filtre sur la colonne WipOrderNo avec la valeur 4631768
-> la ligne suivante est ajouté à la requête (récupérée via l'activity monitor):

Code :

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

WHERE (( ( I2HH.ACTIVE = N'1' )  AND ( I2HD.WipOrderNo LIKE N'%4631768%' ESCAPE '\' )  ) ) ORDER BY I2HH.ID ASC, I2HD.ID ASC

Le problème est que la partie "ESCAPE '\'" change le plan d'exécution et fait passer le temps d'exécution d'environ 6s à 56s.
Dans le cas où on retire la partie escape, SQL fait un scan de l'index clusterisé avec le prédicat sur le WipOrderNo :


Dans l'autre cas, SQL fait un scan sur l'index non-clusterisé (qui est sur le champs Inventory2HistoryHeaderID) sans aucun prédicat (6 436 798 lignes) et fait un key-lookup avec cette fois-ci le prédicat (27 lignes).
Ensuite il fait des nested loops sur ces résultats, ce qui prend plus de 50s.


Le problème est qu'on ne peut pas virer l'escape, il s'agit du comportement de base d'Apriso.
En incluant le champs qui est filtré dans l'index qui est utilisé avec l'échappement on retombe sur de bonnes performances mais je trouves ça un peu dommage de devoir inclure tous les champs qui vont être amené à être filtré.

Est-ce que vous avez une idée de pourquoi le fait d'échapper un caractère peut modifier le plan d'exécution ? (Et si vous avez une meilleure solution)

Merci beaucoup de votre attention !

[escartefigue]

Concernant les perfs : il est clair que quand on encadre une chaîne de caractères avec le signe "%" ou "_" de part et d'autre, aucun index n'est éligible.
En effet, un index n'est éligible que s'il y a égalité sur la totalité ou le début de la chaîne recherchée, or avec un % en début de chaîne, ce n'est pas possible.
Autrement dit, ce n'est pas l'escape qui est en cause, mais la chaîne qui commence par un wildcard %
Pour ce cas de figure, seul un index fulltext pourrait convenir.

De plus, je ne comprends pas : le caractère d'échappement choisi n'est pas présent dans la chaîne recherchée, du coup l'escape est inutile.

[Shkrups]

On est bien d'accord sur l'utilité du caractère d'échappement mais c'est le comportement du logiciel qui ne peut pas être changé.

Comme je disais, cette ligne est ajouté automatiquement, on n'a pas de contrôle dessus.

En gros on a la grille suivante :

Qui correspond à la requête :

Code :

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

1
2
3
4
SELECT *
FROM INVENTORY2_HISTORY_HEADER I2HH
JOIN INVENTORY2_HISTORY_DETAIL I2HD
ON I2HH.ID = I2HD.InventoryHistoryHeaderID

Et quand on rempli le champ WipOrderNo (No. d'Ordre en-cours en français) :

La requête est modifiée est correspond à :

Code :

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

1
2
3
4
5
SELECT *
FROM INVENTORY2_HISTORY_HEADER I2HH
JOIN INVENTORY2_HISTORY_DETAIL I2HD
ON I2HH.ID = I2HD.InventoryHistoryHeaderID
WHERE (( ( I2HH.ACTIVE = N'1' )  AND ( I2HD.WipOrderNo LIKE N'%4630634%' ESCAPE '\' )  ) ) ORDER BY I2HH.ID ASC, I2HD.ID ASC

Je penchais vers l'escape comme coupable car quand je joue la requête sans (cf la requête suivante) j'obtiens le premier plan d'exécution que j'ai mentionné.

Code :

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

1
2
3
4
5
SELECT *
FROM INVENTORY2_HISTORY_HEADER I2HH
JOIN INVENTORY2_HISTORY_DETAIL I2HD
ON I2HH.ID = I2HD.InventoryHistoryHeaderID
WHERE (( ( I2HH.ACTIVE = N'1' )  AND ( I2HD.WipOrderNo LIKE N'%4631768%' )  ) ) ORDER BY I2HH.ID ASC, I2HD.ID ASC

Je peux rajouter que le problème intervient quand il y a à la fois l'escape et l'ORDER BY sur I2HH.ID (pour rappel I2HH.ID = I2HD.InventoryHeaderID donc ceci explique en parti pourquoi il scan l'index non clusterisé même si ça reste moins performant)

Est-ce que ça peut avoir un impact le fait qu'il estime un estimated number of rows for all execution de 11187.8 vs un actual de 6 436 798 ?

[SQLpro]

Globalement ce logiciel semble très mal conçu. En effet lorsqu'une recherche rajoute systématiquement des joker et un like, c'est que la forme normale n°1 n'est probablement pas respecté. Cela oblige l'optimiseur à ignorer les recherches dans un index et faire du balayage.
Au niveau de l'estimation des cardinalités, MS SQL Server est l'un des rares SGBD relationnel à s'en sortir correctement pour estimer une cardinalité avec du LIKE et des jokers... Sur bien d'autres SGBD vous auriez un plan d'exécution exécrable...

Néanmoins si vous êtes en édition Enterprise de SQL Server, vous pouvez forcer un plan de requête à la volée à l'aide des repères de plan.

En édition standard et depuis la version 2016 vous pouvez utiliser le magasin de requêtes pour traquer ce type de requête et forcer l'un des plans les plus rapide.

A +