Bonjour tout le monde,

Je cherche une solution pour optimiser une procédure stockée car elle prend trop de temps (3-4 secondes).

Cette procédure stockée attaque 2 tables avec un index column store.

Est-ce que quelqu'un aurait des astuces ou conseils pour améliorer la rapidité ?

Voici le code :

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
 
CREATE PROCEDURE [dbo].[GetOperationResultsTotalGoodBadLast]
	@SelectionGroupID int = 3469, 
	@OperationType int = 0,
	@CaliberID int = NULL,
	@IcID int = NULL,
	@ModelID int = NULL,
	@ProgramNameID int = NULL,
	@TestID int = NULL,
	@ProgramID int = NULL,
	@RecipeID int = NULL,
	@FactoryID int = NULL,
	@DeviceGroupID int = NULL,
	@DeviceID int = NULL,
	@OrderNumber nvarchar(25) = NULL,
	@OrderOperation nvarchar(5) = NULL,
	@DateStart datetime2(7) = '1900.01.01',
	@DateEnd datetime2(7) = '2999.01.01'
 
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
 
 
	-- 
	-- Create tempory table
	-- 
	CREATE TABLE #OperationResultTmp (
		 [PieceUID] bigint,
		 [DateStart] datetime2(7)
	)
 
 
 
	-- 
	-- Request
	-- 
	INSERT INTO #OperationResultTmp ([PieceUID], [DateStart])
	SELECT [o].[PieceUID], 
		   MAX([o].[DateStart]) AS [DateStart]
 
	FROM [dbo].[OperationResults] [o]
	INNER JOIN [dbo].[Pieces] [p] ON [o].[PieceUID] = [p].[PieceUID]
 
	WHERE ([p].[SelectionGroupID] = @SelectionGroupID) AND
			([p].[Deleted] = 0) AND
			([o].[Type] = @OperationType) AND
			(@CaliberID IS NULL OR [o].[SelectionOR10] = @CaliberID) AND
			(@IcID IS NULL OR [o].[SelectionOR11] = @IcID) AND
			(@ModelID IS NULL OR [o].[SelectionOR12] = @ModelID) AND
			(@ProgramNameID IS NULL OR [o].[SelectionOR01] = @ProgramNameID) AND
			(@TestID IS NULL OR [o].[SelectionOR02] = @TestID) AND
			(@ProgramID IS NULL OR [o].[SelectionOR03] = @ProgramID) AND
			(@RecipeID IS NULL OR [o].[SelectionOR04] = @RecipeID) AND
			(@FactoryID IS NULL OR [o].[SelectionOR07] = @FactoryID) AND
			(@DeviceGroupID IS NULL OR [o].[SelectionOR08] = @DeviceGroupID) AND
			(@DeviceID IS NULL OR [o].[SelectionOR09] = @DeviceID) AND
			(@OrderNumber IS NULL OR [o].[OperationInfoID] IN (SELECT [o1].[OperationInfoID] FROM dbo.OperationInfos [o1] WHERE [o1].[OrderNumber] LIKE @OrderNumber AND (@OrderOperation IS NULL OR [o1].[OrderOperation] LIKE @OrderOperation))) AND
			(DATEDIFF(minute, @DateStart, [o].[DateStart])) >= 0 AND
			(DATEDIFF(minute, @DateEnd, [o].[DateStart])) <= 0
 
	GROUP BY [o].[PieceUID]
	ORDER BY [o].[PieceUID]
 
 
 
	-- 
	-- Select and return results
	-- 
	SELECT COUNT(*) AS [Total],
	            SUM(CASE WHEN [o].[Flag] = 0 THEN 1 ELSE 0 END) AS [Good]
 
	FROM [dbo].[OperationResults] [o]
	INNER JOIN #OperationResultTmp [p] ON [o].[PieceUID] = [p].[PieceUID] AND [o].DateStart = [p].DateStart
 
END
GO
Merci d'avance pour votre aide.