Discussion :

[Snev.dsv]

Bonjour @ tous,

Je viens a vous car j'aimerais optimiser une procedure. Je voudrais dans la mesure du possible, supprimer les curseurs du code suivant:

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
ALTER PROCEDURE [dbo].[CronfulfillOrders]
AS
SET NOCOUNT ON;
 
BEGIN TRAN trfulfillOrders
 
declare 
	@OrderID	varchar(50),
	@POID	varchar(20),
	@FinishDate datetime,
	@IdentifyNumber	varchar(10),
	@KAG	varchar(5),
	@SubID	varchar(10),
	@Order_No_ varchar(50),
	@Task_Date datetime,
	@RefernzNo varchar(30),
	@MandantID varchar(50),
	@ServiceID varchar(20),
	@BestellDate datetime,
	@counter int
 
 
DECLARE OpenOrders CURSOR FOR
 
 
SELECT 
	OrderId,
	POID,
	OrderFinished,
	ReferenceNumber,
	SubID,
	MandatorID,
	ServiceID,
	orderdate
FROM Orders 
WHERE EnwisOrderID is NULL
AND POID is NOT NULL
-- mz 24.04.06 AND StatusID = 60
 
 
OPEN OpenOrders
 
 
FETCH NEXT FROM OpenOrders INTO 
	@OrderID,
	@POID	,
	@FinishDate,
	@RefernzNo,
	@SubID,
	@MandantID,
	@ServiceID,
	@BestellDate
 
 
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
 
	SET @Order_No_ = NULL
	SET @POID = '%' + @POID + '%'
 
 
	SELECT @Order_No_ = Order_No_ , @Task_date = Task_date 
	FROM DW_OrderManagement.dbo.CCRC_Auswertung 
	WHERE ((External_Document_No_ LIKE @POID AND Service_No_ LIKE LEFT(@ServiceID,5) + '%' )
	OR ((External_Document_No_ = @RefernzNo AND @RefernzNo IS NOT NULL AND @RefernzNo <> '')
		AND Service_No_ LIKE LEFT(@ServiceID,5) + '%' )
	OR (Order_No_ = @OrderID))
	AND @SubID = Center_No_
	AND MandantID = @MandantID
	AND Task_date >= @BestellDate - 1
 
 
 
	IF NOT @Order_No_  IS NULL 
	BEGIN
 
		/* Order schreiben */
		UPDATE Orders 
		SET EnwisOrderID = @Order_No_,
		StatusID = 90,
		OrderFinished = ISNULL(@FinishDate, CURRENT_TIMESTAMP),
		TaskDate = @Task_date
		WHERE OrderID = @OrderID
		AND MandatorID = @MandantID
 
		SET @counter = @counter + @@ROWCOUNT
 
 
	END
 
 
	FETCH NEXT FROM OpenOrders INTO 
		@OrderID,
		@POID,
		@FinishDate,
		@RefernzNo,
		@SubID,
		@MandantID,
		@ServiceID,
		@BestellDate
 
END
 
CLOSE OpenOrders
DEALLOCATE OpenOrders
 
IF (@@ERROR <> 0 )
BEGIN
  ROLLBACK TRANSACTION trfulfillOrders
  return @@ERROR
END
ELSE
BEGIN
	COMMIT TRANSACTION trfulfillOrders
	return @counter
END

La premiere partie retourne a peu pres 16000 lignes. Par contre la vue
" DW_OrderManagement.dbo.CCRC_Auswertung " en contien des millions reparties sur plusieurs tables de plusieurs bases de donnees. Ce traitement dure a peu pres 2h sur une grosse machine.

Merci d'avance pour tout ce qui pourrait m'aider a reduire le temps de traitement ou pour toutes remarques.


Snev

[elsuket]

Bonjour,

Tu peux faire avec un WHILE

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
 
DECLARE @NB_ROWS INT;
 
DECLARE @maTable TABLE
(
	ID INT IDENTITY,
	maColonne1 typeDeMaColonne1
	maColonne2 typeDeMaColonne2
)
 
INSERT @maTable
SELECT mesColonnesDeMaVue
FROM maVue;
 
SET @NB_ROWS = SCOPE_IDENTITY();
DECLARE @I INT; SET @I = 1;
 
DECLARE @maVar1 typeDeMaColonne1,
	@maVar2 typeDeMaColonne2;
 
WHILE @I <= @NB_ROWS 
BEGIN
	SELECT @maVar1 = maColonne1,
		@maVar2 = maColonne2 
	FROM @maTable 
	WHERE ID = @I;
	BEGIN TRY
		-- Traitements en fonction de la valeur de mes variables
	END TRY
	BEGIN CATCH
		-- Gestion de l'erreur
	END CATCH;
 
	-- Passage à l'enregistrement suivant
	SET @I = @I + 1;
END;

J'espère que ça te servira de piste et que ce sera plus rapide

[benji_dv]

Salut,
As tu pensé à placer un (ou plusieurs) inner join dans ton update,
ce qui permettrair d'éviter le curseur (me semble t il),
après, c'est plus facile de voire cela lorsque l'on dispose du MPD !

[Snev.dsv]

Merci pour l'aide.

Je vais mettre en place le While pour voir, meme si je suis pas specialement convicu que ca apporte quelquechose. Je laisse le profiler juger.

Quant au Inner Join, je ne vois pas vraiement comment je peux le faire, c'est plus une question d'incompetence de ma part.

Bref j'ai de quoi avancer on verra ce aue ca donne. Je vous tiendrais au courant de la suite.

Merci encore. Si vous avez d'autres idees je suis preneur...

@+

[benji_dv]

Ok,
le while peut te résoudre un problème de perf, mais surtout, si ta store proc plante, le curseur risque de demeurer ouvert (si je ne me trompe pas)...
donc c'est déjà une bonne chose !
pour les update avec inner join, base toi sur le MPD, et fait la sélection pour obtenir les lignes à modifier, puis adapte la partie select en update...
en général c ok,
mais évidemment ca dépends directement du MPD, et donc des relations entre tables !
@+
BJ