Discussion :

[Avatar]

Bonjour,

J'ai une table COMMANDES de la forme suivante :

Code :

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

1
2
3
4
5
6
7
 
| COMMANDE | ARTICLE | QTE |
---------------------------
|  1       | ART1    | 5   |
|  2       | ART1    | 15  |
|  3       | ART1    | 25  | 
---------------------------

J'ai une table CATEGORIE de catégories de prix qui va dire que si on achète entre 0 et 5 articles on paye tel prix, entre 5 et 20 tel prix et plus de 20 un autre prix.

Code :

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

1
2
3
4
5
6
7
 
| C_ARTICLE | C_QTE | PRIX |
---------------------------
| ART1      | 0     | 3    |
| ART1      | 5     | 2    |
| ART1      | 20    | 1    |
---------------------------

La table catégorie est une vue et n'a pas d'index

Je voudrais pour chaque ligne de commande avoir la borne inférieure et la borne suppérieur de la catégorie de prix de l'article en fonction de la quantité commandée.

Par exemple, si la quantité commandée est 4, la borne inférieure est 0 et la borne supérieure 5. Si la quantité est 20, la borne inférieur et la borne supérieure est 20.

J'aurais comme résultat par exemple:

2 | ART1 | 15 | 5 | 20

Pour cela j'ai écrit la procédure stockée suivante :

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
ALTER PROCEDURE [dbo].[stat] 
	--@codart varchar(30)
 
AS
-- table variable pour stockage resultat
    DECLARE @reports TABLE 
	(
	commande varchar(20), 
	article varchar(30), 
	quantite numeric(28,12), 
	qteup numeric(28,12),
	qtedown numeric(28,12)
	)
 
	DECLARE
	@commande varchar(20), 
	@article varchar(30)
 
BEGIN
	SET NOCOUNT ON;
 
-- on transpose dans les colonne de la table temporaire les lignes de la requête dans stats_ps
	INSERT INTO @reports
		SELECT
		commande, 
		article, 
		quantite, 
		qteup,
		qtedown
		FROM
		COMMANDES
 
 
 
	 DECLARE Q0 CURSOR FOR 
	 SELECT commande, article FROM @reports
	 OPEN Q0 
	 FETCH NEXT FROM Q0 INTO @commande, @article
	 WHILE (@@FETCH_STATUS = 0)
		  BEGIN 
			UPDATE @reports
			SET qtedown = (  -- on veut récupérer la valeur basse de la catégorie de prix
					SELECT
					   max(C_QTE)
					FROM
					   CATEGORIE 
					WHERE 
					  C_ARTICLE = @article 
					  and C_QUANTITE <= @quantite)
			WHERE
				commande = @commande
				and article = @article
 
			UPDATE @reports
			SET qteup = (   --  on veut récupérer la valeur haute de la catégorie de prix
					SELECT
					   min(C_QTE)
					FROM
					   CATEGORIE
					WHERE
					   C_ARTICLE = @article 
					and C_QUANTITE >= (case when @quantite > (select     --si la quantite commande est plus grande que la plus grande des quantités de la catégorie
		max(C_QTE) 
		from CATEGORIE 
		where C_ARTICLE = @article) 
	then (select             -- alors on prend la plus grande quantité de la catégorie pour valeur de qteup
	        max(C_QTE)
	       from CATEGORIE 
	        where C_ARTICLE = @article)
	else @quantite            -- sinon on compare avec la quantité de la commande
	end)
						)
			WHERE
				and commande = @commande
				and article = @article
		  -- suivant
		  FETCH NEXT FROM Q0 INTO @commande, @article
	 END
	 CLOSE Q0
	 DEALLOCATE Q0 
 
 
END
 
SELECT 
	*
FROM
	@reports

J'avoue que le deuxième update est pas très propre mais même si je l'enlève, je me retrouve avec un temps d'execution très long.

Pour avoir une idée, avec 6000 lignes dans la table COMMANDES (qui ne concernent qu'un seul article) et 150 lignes dans la table CATEGORIE (dont 5 ligne concernant l'article de la table COMMANDE), je dois attendre 20 mn pour avoir le résultat.

Et en fait ma table COMMANDES contient 45000 lignes et environ 140 articles....

Auriez-vous une idée pour optimiser ce code?

Merci.

[Waldar]

Vos données :

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
-- Categories
 
DECLARE @Categorie TABLE
(
    C_ARTICLE    varchar(4),
    C_QTE        integer,
    PRIX         integer
);
 
INSERT INTO @Categorie (C_ARTICLE, C_QTE, PRIX)
SELECT 'ART1', 0 , 3 UNION ALL
SELECT 'ART1', 5 , 2 UNION ALL
SELECT 'ART1', 20, 1;
 
 
-- Commandes
 
DECLARE @Commande TABLE
(
    COMMANDE    integer identity,
    ARTICLE     varchar(4),
    QTE         integer
);
 
INSERT INTO @Commande (ARTICLE, QTE)
SELECT 'ART1',  5 UNION ALL
SELECT 'ART1', 15 UNION ALL
SELECT 'ART1', 25;

Création d'une table intermédiaire pour vos catégorie : ça peut être une vue, ça peut être une modification de votre vue existante si celle-ci ne sert pas pour d'autres besoin :

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
DECLARE @Cat TABLE
(
    C_ARTICLE    varchar(4),
    C_QTE_MIN    integer   ,
    C_QTE_MAX    integer   ,
    PRIX         integer
);
 
WITH CAT_TEMP (C_ARTICLE, C_QTE, PRIX, RN) AS
(
SELECT C_ARTICLE, C_QTE, PRIX,
       ROW_NUMBER() OVER(PARTITION BY C_ARTICLE ORDER BY C_QTE ASC)
  FROM @Categorie
)
INSERT INTO @Cat (C_ARTICLE, C_QTE_MIN, C_QTE_MAX, PRIX)
SELECT T1.C_ARTICLE, T1.C_QTE, T2.C_QTE, T1.PRIX
  FROM CAT_TEMP as T1
       LEFT OUTER JOIN CAT_TEMP as T2
         ON T2.RN = T1.RN + 1;
 
 
SELECT C_ARTICLE, C_QTE_MIN, C_QTE_MAX, PRIX
  FROM @Cat;
 
 
C_ARTICLE C_QTE_MIN   C_QTE_MAX   PRIX
--------- ----------- ----------- -----------
ART1      0           5           3
ART1      5           20          2
ART1      20          NULL        1

Requête finale :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT CD.COMMANDE, CD.ARTICLE, CD.QTE,
       CT.C_QTE_MIN, CT.C_QTE_MAX, CT.PRIX
  FROM @Commande AS CD
       INNER JOIN @Cat AS CT
         ON CT.C_ARTICLE = CD.ARTICLE
        AND  CT.C_QTE_MIN <= CD.QTE
        AND (CT.C_QTE_MAX > CD.QTE OR CT.C_QTE_MAX IS NULL);
 
COMMANDE    ARTICLE QTE         C_QTE_MIN   C_QTE_MAX   PRIX
----------- ------- ----------- ----------- ----------- -----------
1           ART1    5           5           20          2
2           ART1    15          5           20          2
3           ART1    25          20          NULL        1

[Avatar]

Merci pour cette réponse si rapide, j'y jetterai un coup d'oeil mardi.
Je regrette de ne pas avoir plus de temps à consacrer au sql, j'aimerais pouvoir trouver des requêtes comme la votre tout seul :-)

[Avatar]

J'ai du me débrouiller autrement pour la création de la table intermédiaire car je suis en sql2000 qui ne connait pas ROW_NUMBER()...

Par contre tout le reste est nickel. Merci!