Optimiser SP utilisée dans SSRS

**freedumz** · 22/05/2018, 10h55

Salut a tous
Je suis en train de faire un raport Kanban dans Sql builder 3.0 avec des donnes provenant de project server. J'arrive a afficher mon raport avec les filtres selectionnes mais le soucis vient du temps d'execution du raport, on dans l'ordre de 5-10 minutes, je suis donc en train de chercher comment l'optimiser, j'ai deja supprime quelques lignes mais mon raport mais toujours autant de temps pour s'afficher
Donc mon raport est constitue d'un tableau reprenant les diverses phases et stage d'un workflow
A chaque phase est associe plusieurs stage, j'ai donc delcare une liste dans laquelle j'ai ajoute mon tableau, ce qui me fait donc un nouveau tableau par phase.

Ensuite en dessous de chaque phase, j'appelle un sous rapport ayant pour but de m'afficher les projets en cours ainsi que divers donnes lies a ce projet. Ici, j'ai du aller chercher les donnes dans une liste sharepoint sous format XML.
Je sais que le probleme vient de la car pour afficher cette requete en lui passant les parametres, on a un temps d'execution tourant autour de 10s, or imaginons que mon sous rapport est appelle 60 fois sur mon rapport, voila ce qui explique ce temps d'execution un peu long
Donc voici ma requete:

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
USE [SPD15_SA_ProjectServer_Custom]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Test]
@StageNames          UNIQUEIDENTIFIER = NULL,
@OwnerNames  Varchar(max)
 
AS
BEGIN
SET NOCOUNT ON
       DECLARE       @LIST nvarchar(max) = 'Project Server Workflow Tasks'
       DECLARE @COL_Name nvarchar(max) ='nvarchar1'
       DECLARE @COL_DESCRIPTION nvarchar(max) ='ntext2'
       DECLARE @COL_DUE_DATE nvarchar(max)='datetime2'
       DECLARE @COL_PRCCOMPLETED nvarchar(max)='float1'
       DECLARE @COL_ASSIGNEDTO nvarchar(max)='nvarchar8'
       DECLARE @ProjectUID nvarchar(max)='nvarchar7'
       Declare @COL_OUTCOME nvarchar(max)='nvarchar6'
 
;WITH PhaseTable AS (                    SELECT
              PRJ.ProjectUID, PRJ.ProjectName, PRJ.EnterpriseProjectTypeUID, EPT.ENTERPRISE_PROJECT_TYPE_NAME
              , PRJ.ProjectOwnerResourceUID, PRJ.ProjectOwnerName
              , PRJ.ProjectStartDate as PlannedStart, PRJ.ProjectFinishDate as PlannedFinish
              , PRJ.[ISC Approval]
              , PRJ.[DG/DEPT], PRJ.Program, PRJ.[IT Division], PRJ.[Project Short Description], PRJ.ProjectBaseline0Work BudgetWork, PRJ.[Project Status]
              , STG.StageUID, STG.StageName, WS.StageOrder, STATUS.StageStateDescription, WS.StageEntryDate, WS.StageCompletionDate
              , PHASE.PhaseUID, PHASE.PhaseName,WORKFLOW_ASSOCIATION_NAME AS workflowName,WORKFLOW_ASSOCIATION_UID as WorkflowUID
                     FROM
              [SPD15_SA_ProjectServer_ProjectWebApp].[dbo].MSP_EpmProject_UserView PRJ
              JOIN [SPD15_SA_ProjectServer_ProjectWebApp].[pub].[MSP_ENTERPRISE_PROJECT_TYPES] EPT on EPT.ENTERPRISE_PROJECT_TYPE_UID=PRJ.EnterpriseProjectTypeUID
              LEFT JOIN [SPD15_SA_ProjectServer_ProjectWebApp].[dbo].[MSP_EpmWorkflowStatusInformation] WS on WS.ProjectUID=PRJ.ProjectUID
              LEFT JOIN [SPD15_SA_ProjectServer_ProjectWebApp].[dbo].[MSP_EpmWorkflowStage] STG on STG.StageUID=WS.StageUID
              LEFT JOIN [SPD15_SA_ProjectServer_ProjectWebApp].[dbo].[MSP_EpmWorkflowStatusType] STATUS on STATUS.StageStatusID=WS.StageStatus
              LEFT JOIN [SPD15_SA_ProjectServer_ProjectWebApp].[dbo].[MSP_EpmWorkflowPhase] PHASE on PHASE.PhaseUID=STG.PhaseUID
              WHERE  StageEntryDate IS NOT NULL AND StageCompletionDate IS NULL  AND WS.StageUID=@StageNames 
              AND    @OwnerNames  like '%' + ISNULL( CAST(PRJ.ProjectOwnerResourceUID as nvarchar(max)), 'Not defined') + '%'
              )
 
  , ProjectTable AS ( SELECT 
              D.tp_Created as StartDate
              , ISNULL(D.[tp_ColumnSet].value('(//*[local-name() = sql:variable("@COL_OUTCOME")])[1]', 'nvarchar(max)'),'Not Reviewed') as Outcome
              ,D.[tp_ColumnSet].value('(//*[local-name() = sql:variable("@COL_Name")])[1]', 'nvarchar(max)') as TaskName
              , D.[tp_ColumnSet].value('(//*[local-name() = sql:variable("@COL_DUE_DATE")])[1]', 'Date') as DueDate
              ,D.[tp_ColumnSet].value('(//*[local-name() = sql:variable("@COL_ASSIGNEDTO")])[1]', 'nvarchar(max)') as AssignedTo,
              Replace(right(D.[tp_ColumnSet].value('(//*[local-name() = sql:variable("@COL_DESCRIPTION")])[1]', 'nvarchar(max)'),36),' ','') ProjectUID 
       FROM 
              [SPD15_PMIS].[dbo].[AllLists] L
              JOIN [SPD15_PMIS].[dbo].[AllWebs] W on W.Id=L.tp_WebId
              JOIN [SPD15_PMIS].[dbo].[AllUserData] D on D.[tp_ListId]=L.tp_ID
       where 
              L.tp_Title = 'Project Server Workflow Tasks'
              AND W.FullUrl like '%projects'
              AND [tp_IsCurrentVersion]=1 
              AND [tp_IsCurrent]=1
              AND D.[tp_DeleteTransactionId]=0
       ) 
 
, AggregateTable AS (
Select T.ProjectUID
         ,max(createdDate) AS LastTaskDate
 
              FROM(
SELECT D.tp_Created as createdDate
              ,Replace(right(D.[tp_ColumnSet].value('(//*[local-name() = sql:variable("@COL_DESCRIPTION")])[1]', 'nvarchar(max)'),36),' ','') ProjectUID 
       FROM 
              [SPD15_PMIS].[dbo].[AllLists] L
              JOIN [SPD15_PMIS].[dbo].[AllWebs] W on W.Id=L.tp_WebId
              JOIN [SPD15_PMIS].[dbo].[AllUserData] D on D.[tp_ListId]=L.tp_ID
 
       where 
              L.tp_Title = 'Project Server Workflow Tasks'
              AND W.FullUrl like '%projects'
              AND [tp_IsCurrentVersion]=1 
              AND [tp_IsCurrent]=1
              AND D.[tp_DeleteTransactionId]=0
              ) as T
 
              GROUP BY T.ProjectUID
              )
 
  Select Outcome, PT.ProjectUID, StageName, PhaseUID, DueDate, AssignedTo, StageStateDescription,TaskName, StageUID,PHT.ProjectName, PT.StartDate
   FROM ProjectTable AS PT
  JOIN PhaseTable AS PHT
  on PT.ProjectUID = CAST(PHT.ProjectUID as nvarchar(max))
  JOIN AggregateTable AS AT  
 ON at.ProjectUID=PT.ProjectUID
AND AT.LastTaskDate = PT.StartDate
 
 
END

Avez des idees de ce que je pourrais faire pour ameliorer cette requete ou mon rapport afin d'obtenir un temps d'attente acceptable?

Edit: Apres plusieurs essais, le soucis vient clairement de mes requetes XML, une idee de la facon dont je pourrais gerer cela? Stocke les resultats dans une table, serait il plus efficace que d'appeler a chaque fois ma procedure stockee?

Merci d'avance

**SQLpro** · 23/05/2018, 17h05

Le XML est par nature itératif et le SQL par nature ensembliste. Bref, vous prenez une fusée et lui demander de faire escale tous les 200 m....

A +

**freedumz** · 28/05/2018, 15h19

Okay c'etait clairement le genre de reponse que je voulais obtenir, j'ai reussi a optimiser ma procedure stocke mais il y a toujours des elements prenant une plombe, donc je ne pourrais donc jamais obtenir les temps souhaites du au XML

Merci pour votre retour

**SQLpro** · 29/05/2018, 07h54

Tentez d'indexer le XML, mais vue que vous faites appel à des fonctions génériques comme local-name() je ne suis pas sur que cela va améliorer les choses....

A +

Optimiser SP utilisée dans SSRS

Développement SQL Server

Discussions similaires

Partager

Partager