Optimisation possible ?

**sasafca** · 08/02/2018, 08h48

Bonjour,

est-ce que vous seriez s'il est possible d'optimiser cette requête qui prend trop de temps svp ? Je ne suis pas arrivé à faire mieux que cela...

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
SELECT distinct BOOK.totoNO,
                                  PA.[OCODPASSENGERID],
                                  titiE_REDUIT.CABINNO ,titiE_REDUIT.titiEID, titiE_REDUIT.totoSTATUSCODE,titiE_REDUIT.CATEGORYCODE           
                                                  FROM DWH_toto.[dbo].[ODS_BKIN_toto_INFO] BOOK     
                                                  INNER JOIN DWH_toto.[dbo].[ODS_CTRL_BK_CalculVersiontoto_Libelle] VERS_BOOK      
                                                  ON (BOOK.ID_SOURCE = VERS_BOOK.ID_SOURCE and VERS_BOOK.FlagVersiontoto = 1)                                                               
 
                                                  INNER JOIN 
                                                  (
                                                               SELECT titi.ID_BKIN ,titi.titiEID ,titi.CABINNO, titi.ID_BKCS , 
                                                                                 titi.totoSTATUSCODE,titi.CATEGORYCODE 
                                                               FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titi 
                                                                               INNER JOIN 
                (              
 
								SELECT titiG.ID_BKIN ,titiG.titiEID ,titiG.CABINNO, MAX(titiG.ID_BKCS) as MAX_ID_BKCS              
                                FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG
								INNER JOIN DWH_toto.[dbo].[ODS_BKCP_titiE_PARTICIPANT] CRUIP ON  CRUIP.ID_BKCS = titiG.ID_BKCS
								INNER JOIN DWH_toto.[dbo].[ODS_BKPA_PARTICIPANT] PA ON PA.PASSENGERNO = CRUIP.PASSENGERNO
								WHERE PA.OCODPASSENGERID IS NOT NULL AND not exists(select 1 
                                FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG1 
								WHERE titiG1.ID_BKIN =  titiG.ID_BKIN AND titiG.titiEID=titiG1.titiEID AND titiG.CABINNO=titiG1.CABINNO
								AND titiG1.totoSTATUSCODE <> titiG.totoSTATUSCODE)  GROUP BY titiG.ID_BKIN ,titiG.titiEID,titiG.CABINNO
								UNION
								SELECT titiG.ID_BKIN ,titiG.titiEID ,titiG.CABINNO, MAX(titiG.ID_BKCS) as MAX_ID_BKCS           
                                FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG
								INNER JOIN DWH_toto.[dbo].[ODS_BKCP_titiE_PARTICIPANT] CRUIP ON  CRUIP.ID_BKCS = titiG.ID_BKCS
								INNER JOIN DWH_toto.[dbo].[ODS_BKPA_PARTICIPANT] PA ON PA.PASSENGERNO = CRUIP.PASSENGERNO
								WHERE PA.OCODPASSENGERID IS NOT NULL AND titiG.totoSTATUSCODE = 'BKD' and exists(select 1 
                                FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG1 
								WHERE titiG1.ID_BKIN =  titiG.ID_BKIN AND titiG.titiEID=titiG1.titiEID AND titiG.CABINNO=titiG1.CABINNO
								AND titiG1.totoSTATUSCODE = 'CXL' )  GROUP BY titiG.ID_BKIN ,titiG.titiEID,titiG.CABINNO
 
 
 
 
                                                                               ) titiGG
                                                               ON titi.ID_BKIN = titiGG.ID_BKIN
                                                               AND titi.ID_BKCS = titiGG.MAX_ID_BKCS
                                                               ) titiE_REDUIT
                                                  ON BOOK.ID_BKIN = titiE_REDUIT.ID_BKIN
 
                                                  INNER JOIN DWH_toto.[dbo].[ODS_BKCP_titiE_PARTICIPANT] CRUIP
                                                  ON titiE_REDUIT.ID_BKCS = CRUIP.[ID_BKCS]
                                                  INNER JOIN  DWH_toto.[dbo].[ODS_BKPA_PARTICIPANT] PA    
                                                  ON (PA.ID_BKIN = BOOK.ID_BKIN
                                                  AND CRUIP.PASSENGERNO = PA.PASSENGERNO and PA.[OCODPASSENGERID] is not NULL)