Optimisation de requêtes

**patricia77360** · 26/06/2013, 16h57

Bonjour à tous,
J'ai une requête d'un de nos développeurs à optimiser au mieux possible :

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
exec sp_executesql N'SELECT [t10].[TaskId], [t10].[TaskTypeId], [t10].[StorageDate], [t10].[BrandId], [t10].[Comment], [t10].[CreatedBy], [t10].[TaskStatusId], [t10].[TaskPriorityId], [t10].[TaskActionTypeId], [t10].[AssignedToTaskBoxId], [t10].[AssignedToUser], [t10].[value] AS [AssignedTo], [t10].[DueDate], [t10].[StorageDate2] AS [TaskActionStorageDate], [t11].[TaskActionContextId], [t11].[TaskActionContextTypeId] AS [TaskActionContextType], (
    SELECT COUNT(*)
    FROM (
        SELECT NULL AS [EMPTY]
        FROM [dbo].[TaskActionContext] AS [t12]
        WHERE [t12].[TaskActionId] = [t10].[TaskActionId]
        ) AS [t13]
    ) AS [value]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t6].[TaskId] DESC, [t6].[TaskId], [t6].[TaskActionId], [t6].[TaskBoxId], [t6].[TaskBoxId2], [t7].[TaskWorkflowId], [t8].[BrandId], [t8].[TaskWorkflowId]) AS [ROW_NUMBER], [t6].[TaskId], [t6].[TaskTypeId], [t6].[StorageDate], [t6].[BrandId], [t6].[Comment], [t6].[CreatedBy], [t6].[TaskStatusId], [t6].[TaskPriorityId], [t6].[TaskActionTypeId], [t6].[AssignedToTaskBoxId], [t6].[AssignedToUser], [t6].[value], [t6].[DueDate], [t6].[StorageDate2], [t6].[TaskActionId]
    FROM (
        SELECT [t0].[TaskId], [t0].[TaskTypeId], [t0].[StorageDate], [t0].[BrandId], [t1].[Comment], [t1].[CreatedBy], [t1].[TaskStatusId], [t0].[TaskPriorityId], [t1].[TaskActionTypeId], [t1].[AssignedToTaskBoxId], [t1].[AssignedToUser], COALESCE([t1].[AssignedToUser],
            (CASE 
                WHEN [t5].[test] IS NOT NULL THEN CONVERT(NVarChar(256),[t5].[Name])
                ELSE CONVERT(NVarChar(256),@p0)
             END)) AS [value], [t1].[DueDate], [t1].[StorageDate] AS [StorageDate2], [t1].[TaskActionId], [t1].[TaskId] AS [TaskId2], [t3].[test], [t3].[TaskBoxStatusId], [t3].[TaskBoxId], [t5].[TaskBoxId] AS [TaskBoxId2]
        FROM [dbo].[Task] AS [t0]
        INNER JOIN ([dbo].[TaskAction] AS [t1]
            LEFT OUTER JOIN (
                SELECT 1 AS [test], [t2].[TaskBoxId], [t2].[TaskBoxStatusId]
                FROM [dbo].[TaskBox] AS [t2]
                ) AS [t3] ON [t1].[AssignedToTaskBoxId] = ([t3].[TaskBoxId])) ON [t0].[TaskId] = [t1].[TaskId]
        LEFT OUTER JOIN (
            SELECT 1 AS [test], [t4].[TaskBoxId], [t4].[Name]
            FROM [dbo].[TaskBox] AS [t4]
            ) AS [t5] ON [t1].[AssignedToTaskBoxId] = ([t5].[TaskBoxId])
        ) AS [t6]
    INNER JOIN [dbo].[TaskWorkflow] AS [t7] ON [t6].[TaskTypeId] = [t7].[TaskTypeId]
    INNER JOIN [dbo].[TaskBrandWorkflow] AS [t8] ON ([t6].[BrandId] = [t8].[BrandId]) AND ([t7].[TaskWorkflowId] = [t8].[TaskWorkflowId])
    WHERE (([t7].[JuniorsCanSeeOtherUsersTasks] = 1) OR ([t6].[AssignedToUser] IS NULL) OR ((NOT ([t7].[JuniorsCanSeeOtherUsersTasks] = 1)) AND (LOWER([t6].[AssignedToUser]) = @p1))) AND ([t6].[TaskActionId] = ((
        SELECT MAX([t9].[TaskActionId])
        FROM [dbo].[TaskAction] AS [t9]
        WHERE [t9].[TaskId] = [t6].[TaskId2]
        ))) AND ([t6].[TaskStatusId] IN (@p2)) AND (([t6].[test] IS NULL) OR (([t6].[test] IS NOT NULL) AND ([t6].[TaskBoxStatusId] <> @p3)))
    ) AS [t10]
LEFT OUTER JOIN [dbo].[TaskActionContext] AS [t11] ON [t11].[TaskActionId] = [t10].[TaskActionId]
WHERE [t10].[ROW_NUMBER] BETWEEN @p4 + 1 AND @p4 + @p5
ORDER BY [t10].[ROW_NUMBER], [t11].[TaskActionContextId]',N'@p0 nvarchar(4000),@p1 varchar(8000),@p2 int,@p3 int,@p4 int,@p5 int',@p0=N'',@p1='load.user63@axa-direct.com',@p2=1,@p3=2,@p4=0,@p5=30

Merci à tous pour la réponse que vous voudrez bien me faire parvenir.