Optimisation Union All SQL SERVER 2k5 [Résolu]

[Blacknounou]

Bonjour,

Débutant sur SQL SERVER , je souhaiterais optimiser ces requêtes jointes par la syntaxe UNION ALL.

Pouvez-vous m'aider ?

Ci-dessous les requêtes concernées

Code :

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
90
91
92
93
94
95
96
97
98
99
100
101
 
 
SELECT
		DISTINCT 'XXX' AS FlowType ,
		rh.delivery_memo, 
		NULL AS sender, 
		rh.store_code_id AS dest, 
		rh.creation_date AS dt, 
		rdl.product_id,
		rdl.color_id,
		rdl.size_id,
		abs(rdl.loose_qty_rm) AS qty, 
		NULL AS reason_id,
		rdl.landed_price AS avg_cost, 
		rdl.price_retail AS retail_price
 
		FROM rm_header rh (nolock) 
			INNER JOIN rm_detail_loose rdl (nolock) ON rh.rm_number = rdl.rm_number AND rh.store_code_id = rdl.store_code_id 
			--and rh.store_code_id = 88
			WHERE 
			(
			   (convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112)) 
				AND rh.rm_status = 'R' AND rh.reception_status = 'N' 
				)
				OR 
				(convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112)) 
				AND
				convert(int,convert(char(8), rh.modified_date, 112)) > convert(int,convert(char(8),@date,112)) AND
					rh.rm_status = 'R' AND rh.reception_status = 'C'
				)
			)
		AND ( rh.delivery_memo IS NULL OR ( rh.delivery_memo IS NOT NULL AND rh.delivery_memo NOT LIKE 'ZZZ%'))
 
 
		union ALL
 
 
SELECT  
                         DISTINCT 'YYY' AS FlowType, 
		NULL AS delivery_memo, 
		th.transfer_from_store_id AS sender, 
		th.transfer_to_store_id AS dest, 
		th.transfer_date AS dt, 
		bd.product_id,
		bd.color_id,
		bd.size_id, 
		case when th.transfer_status = 3 then abs(bd.bin_qty_stocks) 
		when th.transfer_status = 5 then abs(troutd.quantity) end AS qty, th.reason_id AS reason_id
		, TROD_PriceCost AS avg_cost, Cession_Price AS retail_price
		FROM bin_header bh (nolock) 
			INNER JOIN bin_detail bd (nolock) ON  bh.bin_id = bd.bin_id AND bh.store_code_id = bd.store_code_id AND bd.store_code_id IN (SELECT store_code_id FROM store (nolock) WHERE store_type = 'V')
			INNER JOIN transfer_header th (nolock) ON bh.bin_id = th.transfer_number
			INNER JOIN trout_header trout (nolock) ON trout.transfer_number = th.transfer_number 
			INNER JOIN trout_detail troutd (nolock) ON trout.transdate= troutd.transdate AND trout.store_code_id = troutd.store_code_id 
			AND trout.till_id=troutd.till_id AND trout.transnum=troutd.transnum AND trout.transtime=troutd.transtime
			AND troutd.product_id = bd.product_id AND troutd.color_id = bd.color_id AND troutd.size_id = bd.size_id
		WHERE ((th.transfer_status = 3 AND convert(int,convert(char(8), th.transfer_date, 112)) <= convert(int,@date)) 
			OR 
			(th.transfer_status = 5 AND convert(int,convert(char(8), th.transfer_date, 112)) <= convert(int,@date)
			AND NOT EXISTS (SELECT 'f' FROM trin_header inh WHERE th.transfer_number = inh.transnum AND
					th.transfer_to_store_id = inh.store_code_id AND 
					convert(char(8), inh.transdate, 112) <= CONVERT(char(8),@date,112))
			))
		AND trout.approved = 'Y'
 
		union ALL
 
SELECT
                        DISTINCT 'ZZZ' AS FlowType, 
                         rmh.delivery_memo,
		rh.store_code_id AS sender, 
                         sdest.store_code_id AS dest, 
                          rmh.creation_date AS dt, 
		rd.product_id,
		rd.color_id,
		rd.size_id,
		abs(rd.loose_qty_rtv) AS qty, r.reason_id, 
		rdl.landed_price AS avg_cost, rdl.price_retail AS retail_price
		FROM  
			rtv_header rh (nolock) 
			INNER JOIN store s1 (nolock) ON rh.store_code_id = s1.store_code_id
			INNER JOIN rtv_detail_loose rd (nolock) ON rh.rtv_number = rd.rtv_number
			INNER JOIN reasons r (nolock) ON r.reason_id = rh.reason_id
			INNER JOIN store sdest (nolock) ON convert(nvarchar(8),sdest.store_code_id) = r.mq_queue_sender
			INNER JOIN rm_header rmh (nolock) ON rtrim(rmh.delivery_memo) LIKE 'VIS%'+ rtrim(convert(varchar,rh.rtv_number)) 
			INNER JOIN rm_detail_loose rdl (nolock) ON rmh.rm_number = rdl.rm_number AND rmh.store_code_id = rdl.store_code_id 
			AND rd.product_id = rdl.product_id AND rd.color_id = rdl.color_id AND rd.size_id = rdl.size_id
		WHERE 
			(
			   (convert(int,convert(char(8), rmh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112)) 
				AND rmh.rm_status = 'R' AND rmh.reception_status = 'N' 
				)
				OR 
				(convert(int,convert(char(8), rmh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112)) 
				AND
				convert(int,convert(char(8), rmh.modified_date, 112)) > convert(int,convert(char(8),@date,112)) AND
					rmh.rm_status = 'R' AND rmh.reception_status = 'C'
				)
			)
		AND rh.approved = 'Y' AND  convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112))
		AND r.description LIKE 'ZZZ%'

[aieeeuuuuu]

bonjour

Code SQL :

1
2
3
4
5
 
WHERE 
			(
			   (convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112)) 
...

Quel est le type de vos colonne dates ?
(et de votre paramètre date)

il ne serait pas inutile de fournir la structure des tables et des index

[Blacknounou]

Bonjour ,

Le type de la variable @date

Code :

1
2
3
 
declare @date char(8)
SELECT @date = convert(char(8), dateadd(d, -1, getdate()),112)

[aieeeuuuuu]

et le type de vos colonnes ???

pourquoi ne pas utiliser le type DATE ?

quelle est votre version de SQL Server ?

Combien de temps met la requête a s’exécuter ?

[Blacknounou]

Type de mes colonnes date : "datetime"

Version de SQL SERVER 2k5 :
9.00.3042.00 SP2 Enterprise Edition (64-bit)

La requête met 1min30s

[aieeeuuuuu]

Commencez par remplacer la déclaration/initialisation de @date par ceci :

Code SQL :

1
2
3
 
DECLARE @date AS DATETIME
SET @date =  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1 , 0)

puis remplacez tous vos :

Code SQL :

1
2
 
convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112))

Par

Code SQL :

1
2
 
rh.creation < @date

Indiquez nous aussi quels sont les index sur ces tables.

Aussi, pouvez-vous expliquer le but/contexte de ceci :

Code SQL :

1
2
3
 
			INNER JOIN store sdest (nolock) ON convert(nvarchar(8),sdest.store_code_id) = r.mq_queue_sender
			INNER JOIN rm_header rmh (nolock) ON rtrim(rmh.delivery_memo) LIKE 'VIS%'+ rtrim(convert(varchar,rh.rtv_number))

[iberserk]

Les rtrim sont également clairement à bannir!

AND ( rh.delivery_memo IS NULL OR ( rh.delivery_memo IS NOT NULL AND rh.delivery_memo NOT LIKE 'ZZZ%'))

Voir du côté du NOT LIKE 'ZZZ%' qui pourrait peut être avantageusement être remplacé par un 'etat' dans votre table de type bit... ('estZZZ' oui/non).

[Blacknounou]

Merci pour vos conseils!!

Cela à améliorer le temps de réponse.

Merci !