Optimiser une procédure stockée
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:
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.
