Discussion :

[FMJ]

Je vous sollicite au sujet d'une requête qui me donne quelques soucis.
Le premier point à considérer, c'est que je ne peux pas en changer la structure des tables, ni influer sur les règles de gestion précisées ci-dessous. Je peux juste jouer sur la requête et les index.

La requête a pour but de déterminer le prix d'un article, sachant que le prix peut être :
> unique par site ou bien le même pour tous les sites
> unique par dimension (couleur, taille, etc.) ou bien le même pour toutes les dimensions de l'article
De plus on recherche le dernier prix pour cet article en terme de date de création, sachant qu'il peut bien évidemment avoir un historique de plusieurs prix.

En fait, c'est cette triple hypothèse qui va me poser des problèmes de perf lors de l'exécution de la requête de recherche de prix, même si le nombre d'articles et de prix n'est vraiment pas démentiel (23 000 articles et 33 000 prix).

Voici les champs de la table utilisés dans la requête :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE ARTICLEPRIX
(
SITE CHAR(1)      --> site auquel le prix est à appliquer
ARTICLE CHAR(2)   --> code de l'article
DATE SMALDATETIME --> date de début du prix
PRIX INTEGER      --> prix de l'article pour un site et une date donnés
)

Si le champ SITE='A', cela veut dire que le prix est spécifique au site A.
Si le champ SITE='', cela veut dire que le prix est commun à l'ensemble des sites.

Article non dimensionné : ARTICLE='1 ' où '1' est le code de l'article et ' ' signifie qu'il n'a pas de dimension
Article dimensionné : ARTICLE='2G' où '2' est le code de l'article et 'G' correspond à la dimension G.

Si pour l'article n°3 dimensionné, le prix est commun à l'ensemble des dimensions, alors une seule ligne de prix est crée avec ARTICLE='3 ' (donc comme pour un article non dimensionné)
Si pour un article n°4 dimensionné, le prix est spécifique pour une ou plusieurs dimensions, alors une ligne de prix est crée par dimension avec un prix spécifique (ARTICLE='4A', ARTICLE='4B', ARTICLE='4C', etc.) et une ligne est crée pour toutes les autres dimensionnés qui ont un prix commun (ARTICLE='4 ').

Ces règles un peu mieux précisées, voici la requête :

Code :

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

SELECT TOP 1 PRIX FROM ARTICLEPRIX WHERE (SITE=[le site à chercher] OR SITE='') AND (ARTICLE=[code de l''article] OR ARTICLE=LEFT([code de l''article],1) + ' ') AND DATE <= [date à chercher] ORDER BY DATE DESC, ARTICLE DESC, SITE DESC

La requête en elle-même semble toute bête mais les deux clauses (SITE=[le site à chercher] OR SITE='') et (ARTICLE=[code de l'article] OR ARTICLE=LEFT([code de l''article],1) + ' ') augmentent les temps de traitement de façon exponentielle. Par contre si l'on utilise uniquement l'une ou l'autre, les temps de traitement chutent énormément.

La base tourne sur un SQL Server 2000.

En terme d'index, j'ai créé celui-ci qui est parfaitement inefficace :

Code :

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

1
2
CREATE INDEX X_PRIX_INDEX1
 ON ARTICLEPRIX (ARTICLE ASC, SITE ASC, DATE DESC)

Pour donner un ordre d'idée, pour 100 articles, la requête prend 1.5s, mais pour 1000 articles elle prend 38s. Je vous laisse deviner pour 10000 articles ....

Auriez-vous une idée sur la façon d'optimiser la requête ou d'indexer plus efficacement la table ? Merci d'avance.

[Lyche]

Je ne sais pas si tu as remarqué, mais ton index est créé sur la table PRIX et non ARTICLEPRIX (c'est peut-être une erreur de C/C)

[StringBuilder]

Votre modèle des données est tout sauf normalisé.

Et il est fort à parier que vos problèmes de performance sont 100% liés à votre tentative d'optimisation mode années 70 de votre modèle des données.

On n'est plus à l'époque des premières versions de Ingres. On peut faire des jointures dans une requête, et répartir les données dans plusieurs entités reliées proprement par des clés étrangères est infiniment plus performant que de faire une table poubelle telle que la vôtre.

Votre table devrait, au mieux, être le résultat d'une vue. Mais clairement pas une table !

[Kropernic]

Votre modèle des données est tout sauf normalisé.

Et il est fort à parier que vos problèmes de performance sont 100% liés à votre tentative d'optimisation mode années 70 de votre modèle des données.

On n'est plus à l'époque des premières versions de Ingres. On peut faire des jointures dans une requête, et répartir les données dans plusieurs entités reliées proprement par des clés étrangères est infiniment plus performant que de faire une table poubelle telle que la vôtre.

Votre table devrait, au mieux, être le résultat d'une vue. Mais clairement pas une table !

On est bien d'accord mais malheureusement, comme dans beaucoup d'endroits, le pauvre garçon hérite d'un truc déjà fait (et qui date probablement de Mathusalem) et il faut bien faire avec...

Concernant l'index, j'ai pas fait gaffe à la table sur laquelle il porte comme le mentionne lyche mais de toute façon, une requête contenant un OR n'est pas sargeable et n'utilisera donc pas l'index (sauf si vous avez du bol et que l'optimiseur de sql serveur réécrit votre requête sans OR).

Voilà donc déjà une piste. Tentez de réécrire votre requête sans OR (en général, ça marche avec des UNION).

[Kropernic]

Que donne ceci comme résultat ??

N.B. : C'est normal le TOP 1 ???

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
SELECT TOP 1 
        PRIX 
FROM 
        ARTICLEPRIX 
WHERE    
        1 = CASE
                WHEN SITE = [LE SITE A CHERCHER] THEN 1
                WHEN SITE = '' THEN 1
                ELSE 0
            END 
    AND 1 = CASE
                WHEN ARTICLE = [CODE ARTICLE] THEN 1
                WHEN ARTICLE = LEFT([CODE ARTICLE],1) + ' ')
                ELSE 0
            END  
    AND DATE <= [DATE A CHERCHER] 
ORDER BY 
        DATE DESC

[Lyche]

Les UNIONS font clairement gagner en performances. Je voulais poster un aperçut des gains substantiels entre une requête avec des OR ou une requête avec un UNION. Le soucis c'est que le forum n'est pas en forme en ce moment et il plante quand j'essaye.

Bref, avec la requête suivante

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 SELECT TOP 1 PRIX
   FROM ARTICLEPRIX
  WHERE SITES = 'xx'
AND ARTICLE='ok'
AND DATES   <= '20140101'
UNION
 SELECT TOP 1 PRIX
FROM ARTICLEPRIX
WHERE SITES     =''
AND ARTICLE  =LEFT('test',1) + ' '
AND DATES   <= '20140101'
ORDER BY 1 DESC

Vous obtenez le même résultat avec beaucoup moins de consommation.

J'aurais tendance à proposer un

Code sql :

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
SET ROWCOUNT 1;
GO
 
 SELECT PRIX
   FROM ARTICLEPRIX
  WHERE SITES = 'xx'
AND ARTICLE='ok'
AND DATES   <= '20140101'
UNION
 SELECT PRIX
FROM ARTICLEPRIX
WHERE SITES     =''
AND ARTICLE  =LEFT('test',1) + ' '
AND DATES   <= '20140101'
ORDER BY 1 DESC;
 
SET ROWCOUNT 0;
GO

Simplement parce que le TOP x n'est ni une norme SQL, ni une fonctionnalité ensembliste. (mais je chipote)

[StringBuilder]

Juste pour rappel, UNION fait un DISTINCT implicite.
Par conséquent, afin d'avoir le résultat attendu et des performances correctes, il faut absolument toujours faire UNION ALL.

Sinon, à la place d'un UNION, un IN ne permet-il pas d'utiliser l'index sans complexifier la requête ?

Code sql :

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

1
2
3
4
5
6
7
 
SELECT TOP 1 PRIX 
FROM ARTICLEPRIX 
WHERE SITE in ([le site à chercher], '')
AND ARTICLE in ([code de l''article], LEFT([code de l''article],1) + ' ') 
AND DATE <= [date à chercher]
ORDER BY DATE DESC

[Lyche]

les performances entre un IN et un OR sont les mêmes. Le comportement au niveau du moteur SQL est le même.

Et, oui pour le UNION qui fait le distinct implicite, cependant, comme il ne souhaite qu'une seule ligne, je me suis même pas posé la question (cependant cela reste une excellente remarque)

[FMJ]

Je ne sais pas si tu as remarqué, mais ton index est créé sur la table PRIX et non ARTICLEPRIX (c'est peut-être une erreur de C/C)

C'est exact, j'ai corrigé cette boulette.

[FMJ]

Votre modèle des données est tout sauf normalisé.

Et il est fort à parier que vos problèmes de performance sont 100% liés à votre tentative d'optimisation mode années 70 de votre modèle des données.

On n'est plus à l'époque des premières versions de Ingres. On peut faire des jointures dans une requête, et répartir les données dans plusieurs entités reliées proprement par des clés étrangères est infiniment plus performant que de faire une table poubelle telle que la vôtre.

Votre table devrait, au mieux, être le résultat d'une vue. Mais clairement pas une table !

D'une part vous aurez notez que la table donnée en exemple est toute simple : Un article, une site, une date, un prix.
Ce sont les règles de gestion qui sont "un peu" lourdes.
Là-dessus, encore une fois il ne s'agit pas de ma base. C'est le MCD d'un des plus gros éditeurs français.
Et encore, j'ai simplifié en gommant un certain nombre de points qui auraient fait hurler par ici.
En somme je subis et dois faire avec !

[Kropernic]

les performances entre un IN et un OR sont les mêmes. Le comportement au niveau du moteur SQL est le même.

Et, oui pour le UNION qui fait le distinct implicite, cependant, comme il ne souhaite qu'une seule ligne, je me suis même pas posé la question (cependant cela reste une excellente remarque)

Que ce soit l'un ou l'autre, la requête que je propose mets tout le monde d'accord non ? ^^

[FMJ]

Je viens de m'apercevoir qu'en écrivant un peu vite le premier post, j'ai commis un petit oubli. La requête que j'utilise actuellement s'écrit plutôt :

Code :

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

SELECT TOP 1 PRIX FROM ARTICLEPRIX WHERE (SITE=[le site à chercher] OR SITE='') AND (ARTICLE=[code de l''article] OR ARTICLE=LEFT([code de l''article],1) + ' ') AND DATE <= [date à chercher] ORDER BY DATE DESC, ARTICLE DESC, SITE DESC

La différence est dans le ORDER BY. En utilisant les champs dans cet ordre de prix, et ce sens de tri, je trouve le "bon" prix.
L'éditeur lui ne s'embête pas. Il fait une requête globale pour avoir pour un article donné tous les prix toutes dimensions, sites et dates compris C'est ensuite le code de son application qui se charge de trouver le bon prix.

Je vais tester la proposition de Lyche, mais avec 4 UNION car il y a 4 cas de figure :
> SITE=' ' ou SITE='1'
> ARTICLE='2G' ou LEFT(ARTICLE,1)='2'

[aieeeuuuuu]

Même avec un OR, les index peuvent être utilisés, s'ils restent efficaces. Faites nous quand même savoir le résultat de vos tests avec UNION ALLEn revanche, pour votre index, vous devriez ajouter la colonne prix en colonne incluse afin de le rendre couvrant pour la requete :

Code :

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

1
2
3
 
CREATE INDEX X_PRIX_INDEX1
 ON ARTICLEPRIX (ARTICLE ASC, SITE ASC, DATE DESC) INCLUDE(PRIX)

[FMJ]

Je ne serai pas là demain. Réponse jeudi.

Merci en tout cas de votre aide.

[Kropernic]

Même avec un OR, les index peuvent être utilisés, s'ils restent efficaces. Faites nous quand même savoir le résultat de vos tests avec UNION ALLEn revanche, pour votre index, vous devriez ajouter la colonne prix en colonne incluse afin de le rendre couvrant pour la requete :

Code :

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

1
2
3
 
CREATE INDEX X_PRIX_INDEX1
 ON ARTICLEPRIX (ARTICLE ASC, SITE ASC, DATE DESC) INCLUDE(PRIX)

A moins que cela ait changé avec une version plus récent que l'article, les indexes ne sont pas utilisés avec un OR.

[SQLpro]

A moins que cela ait changé avec une version plus récent que l'article, les indexes ne sont pas utilisés avec un OR.

ça dépend des cardinalité estimée. mais avec un LEFT wallou !

De plus il est sur du 2000 donc pas d'include !

1) créez une colonne calculée persistante nommée ARTICLE1 sur LEFT([code de l''article],1) et utilisez là dans votre requête
2) créez l'index suivant : DATE, ARTICLE, SITE, ARTICLE1 , PRIX

A +

[aieeeuuuuu]

Une question me taraude tout de même...

Pour donner un ordre d'idée, pour 100 articles, la requête prend 1.5s, mais pour 1000 articles elle prend 38s. Je vous laisse deviner pour 10000 articles ....

Comment faites vous pour avoir 1000 et même 10000 codes avec un CHAR(1) ?

[StringBuilder]

Je pense qu'il parle non pas de nombre d'article, mais plutôt de lignes (il y a plusieurs dates et sites possibles par article)

[FMJ]

En fait la structure de table et de champs que j'ai donnée est très simplifiée.
Dans la réalité ARTICLE est un CHAR(40). Les exemples que j'ai donnés (100, 1000, 10 000) correspondent bien au nombre de prix, en comptant en moyenne 2 à 3 prix par article.

J'ai d'abord essayé la méthode avec UNION. Pour 10 000 prix, la durée d'exécution tombe à 2s contre plusieurs minutes auparavant ..... Autant dire que ce délai d'exécution me convient parfaitement !
Ca n'aide pas au niveau de la lisibilité de la requête mais on va faire avec.

Je vais essayer la méthode évoquée par François. Initialement, n'ayant jamais manipulé de trigger, je pensais créer une table avec le tarif courant pour chaque article, table qui serait calculée en heure creuse par PS. Ce qui posait, c'est l'absence d'actualisation d'un prix en cours de journée au cas où celui-ci est changé

Je vais donc tâcher d'essayer de créer cette table à partir d'un trigger.

[Kropernic]

J'ai d'abord essayé la méthode avec UNION. Pour 10 000 prix, la durée d'exécution tombe à 2s contre plusieurs minutes auparavant ..... Autant dire que ce délai d'exécution me convient parfaitement !
Ca n'aide pas au niveau de la lisibilité de la requête mais on va faire avec.

Hello,

Car je suis curieux mais surtout car j'aimerais savoir si je ne raconte pas de la merde, as-tu testé la solution que je proposais à base de CASE dans la clause WHERE et sans union ?
Peut-être trouveras-tu cette écriture plus facile à lire. Fonctionnellement, cela devrait être totalement identique à une requête utilisant UNION.