Discussion :

[Elthriel]

Bonjour,

J'ai un problème de performance que je ne m'explique pas entre deux requête censé remonter plus ou moins la même chose. Hors lors de mes test sous SQL Tools je me suis rendu compte que les requêtes s'exécutaient avec les même temps mais dans un cas le fetching se fait en 5sec et dans l'autre il se fait en 1min...

Code de la première

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
SELECT * FROM tbl_trp_trip trp JOIN tbl_mis_mission mis ON TRP.id = mis.trip_id AND TRP.is_deleted = 'N' 
LEFT JOIN tbl_trp_trip_route trip_root ON trp.id = trip_root.trip_id 
LEFT JOIN tbl_com_task TSK ON TSK.id = trip_root.task_id AND TSK.is_deleted = 'N' 
LEFT OUTER JOIN tbl_com_task_product PRD ON TSK.iD=PRD.task_id AND PRD.is_deleted = 'N' 
LEFT OUTER JOIN tbl_ref_product prod ON PRD.product_id=prod.id AND prod.is_deleted = 'N' 
LEFT OUTER JOIN tbl_ref_measurement_unit unit1 ON PRD.measurement_unit_1_id=unit1.id AND prd.real_quantity_1 IS NOT null 
LEFT OUTER JOIN tbl_ref_measurement_unit unit2 on PRD.measurement_unit_2_id=unit2.id AND prd.real_quantity_2 IS NOT null 
LEFT OUTER JOIN tbl_ref_measurement_unit unit3 on PRD.measurement_unit_3_id=unit3.id AND prd.real_quantity_3 IS NOT null 
LEFT JOIN tbl_ref_location LOC ON tsk.location_id = LOC.id AND LOC.is_deleted='N' 
LEFT JOIN (tbl_mis_mission_resource mis_truck JOIN tbl_ref_resource res_truck ON (res_truck.id = mis_truck.resource_id AND res_truck.resource_type_info ='TRUCK')) ON mis_truck.mission_id = mis.id 
LEFT JOIN (tbl_mis_mission_resource mis_container JOIN tbl_ref_resource res_container ON (res_container.id = mis_container.resource_id AND res_container.resource_type_info = 'CONTAINER')) ON mis_container.mission_id = mis.id 
LEFT JOIN (tbl_mis_mission_resource mis_trailer JOIN tbl_ref_resource res_trailer ON (res_trailer.id = mis_trailer.resource_id AND res_trailer.resource_type_info = 'TRAILER')) ON mis_trailer.mission_id = mis.id 
LEFT JOIN (tbl_mis_mission_resource mis_driver JOIN tbl_ref_resource res_driver ON (res_driver.id = mis_driver.resource_id AND res_driver.resource_type_info = 'DRIVER')) ON mis_driver.mission_id = mis.id 
LEFT JOIN tbl_ref_driver drv ON drv.resource_id = res_driver.id 
LEFT JOIN tbl_ref_trailer trl ON trl.resource_id = res_trailer.id 
LEFT JOIN tbl_ref_container ctn ON ctn.resource_id = res_container.id 
LEFT JOIN tbl_ref_truck trk ON trk.resource_id = res_truck.id 
LEFT JOIN TBL_REF_FREIGHT_PAYER FP ON FP.ID = TRP.EXTERNAL_SUBCON_ID 
LEFT JOIN TBL_REF_SITE SITE ON SITE.ID = TRP.INTERNAL_SUBCON_ID 
LEFT JOIN TBL_REF_CONTRACT Contract ON TRP.CONTRACT_ID = Contract.ID 
LEFT JOIN TBL_TRP_TRIP CTRP ON TRP.ID = CTRP.SUBCONTRACTOR_ID
WHERE TRP.ID IN (....)

deuxième requête

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
SELECT * FROM tbl_trp_trip trp LEFT JOIN (
           tbl_trp_trip_route trip_route 
            JOIN tbl_com_task TSK 
              ON (TSK.id = trip_route.task_id AND TSK.is_deleted = 'N')
            LEFT JOIN tbl_com_task_product PRD ON (TSK.iD =PRD.task_id AND PRD.is_deleted = 'N')
            LEFT JOIN tbl_ref_product prod ON (PRD.product_id = prod.id AND prod.is_deleted = 'N')
            --INNER JOIN tbl_trp_trip trp2 ON trip_route.trip_id= trp2.id AND TRP2.IS_FINAL = 'Y'
            LEFT JOIN tbl_mis_mission mis ON trip_route.trip_id = mis.trip_id AND mis.is_deleted = 'N' 
            LEFT JOIN tbl_ref_measurement_unit unit1 ON PRD.measurement_unit_1_id=unit1.id AND prd.real_quantity_1 IS NOT NULL
            LEFT JOIN tbl_ref_measurement_unit unit2 ON PRD.measurement_unit_2_id=unit2.id AND prd.real_quantity_2 IS NOT NULL
            LEFT JOIN tbl_ref_measurement_unit unit3 ON PRD.measurement_unit_3_id=unit3.id AND prd.real_quantity_3 IS NOT NULL
            LEFT JOIN tbl_ref_location LOC ON tsk.location_id = LOC.id AND LOC.is_deleted  ='N'
            LEFT JOIN (tbl_mis_mission_resource mis_truck JOIN tbl_ref_resource res_truck ON (res_truck.id = mis_truck.resource_id AND res_truck.resource_type_info ='TRUCK')) ON mis_truck.mission_id = mis.id
            LEFT JOIN (tbl_mis_mission_resource mis_container JOIN tbl_ref_resource res_container ON (res_container.id = mis_container.resource_id AND res_container.resource_type_info = 'CONTAINER')) ON mis_container.mission_id = mis.id
            LEFT JOIN (tbl_mis_mission_resource mis_trailer JOIN tbl_ref_resource res_trailer ON (res_trailer.id = mis_trailer.resource_id AND res_trailer.resource_type_info = 'TRAILER')) ON mis_trailer.mission_id = mis.id
            LEFT JOIN (tbl_mis_mission_resource mis_driver JOIN tbl_ref_resource res_driver ON (res_driver.id = mis_driver.resource_id AND res_driver.resource_type_info = 'DRIVER')) ON mis_driver.mission_id = mis.id 
          ) 
          ON trip_route.trip_id= trp.id
WHERE TRP.ID IN (...)

Si quelqu'un à une idée d'où peut venir c'est différence de performance ?
Merci d'avance

[Waldar]

Vous avez besoin de toutes les colonnes de toutes ces jointures ?

Dans la majorité des cas vous faites des jointures externes, êtes-vous sûr d'en avoir besoin (elles sont plus coûteuses) ?

Pouvez-vous exécuter un TKPROF de ces deux requêtes ?