Reportnet Report Is Very Slow

[ahlone]

Hi,
Most of the reports are completed within 1-2 minutes, but, I have a report which is taking about 15 minutes to complete. Could someone pls. tell me how to find out why it's taking too long, &, how to fine-tune the report.

HERE IS THE MAIN SQL STATEMENT:

************************************************************
select
Plant.PLANT_NM as Plant_Name,
Movement_Document_Class.DOCUMENT_TYPE_DSC as Document_Type_Description,
cast(T3.TIME_SID as date) as Posted_Date,
Material_Movement_Document.USER_NM as User_Name,
Material_Movement_Document.DOCUMENT_NO as Document_Number,
Material_Movement_Document.ORDER_DOC_ITM_NO as Order_Document_Item_Number,
Material.MATERIAL_ID as Material_Identifier,
Material.MATERIAL_DSC as Material_Description,
Material.MATERIAL_GRP_DSC as Material_Group_Description,
Reverse_Plant.PLANT_ID as Plant_Identifier__Reverse_,
Reverse_Plant.PLANT_NM as Plant_Name__Reverse_,
Unit_of_Measure.UOM_DSC as Unit_of_Measure_Description,
XSUM(T3.MOVEMENT_QTY for Plant.PLANT_NM,Movement_Document_Class.DOCUMENT_TYPE_DSC,cast(T3.TIME_SID as date),Material_Movement_Document.USER_NM,Material_Movement_Document.DOCUMENT_NO,Material_Movement_Document.ORDER_DOC_ITM_NO,Material.MATERIAL_ID,Material.MATERIAL_DSC,Material.MATERIAL_GRP_DSC,Reverse_Plant.PLANT_ID,Reverse_Plant.PLANT_NM,Unit_of_Measure.UOM_DSC ) as Movement_Quantity,
XSUM(T3.MOVEMENT_AMTR for Plant.PLANT_NM,Movement_Document_Class.DOCUMENT_TYPE_DSC,cast(T3.TIME_SID as date),Material_Movement_Document.USER_NM,Material_Movement_Document.DOCUMENT_NO,Material_Movement_Document.ORDER_DOC_ITM_NO,Material.MATERIAL_ID,Material.MATERIAL_DSC,Material.MATERIAL_GRP_DSC,Reverse_Plant.PLANT_ID,Reverse_Plant.PLANT_NM,Unit_of_Measure.UOM_DSC ) as Movement_Amount
from
Mart...PLANT Plant,
Mart...MOVEMENT_DOC_CLSS Movement_Document_Class,
(select
T1.MTRL_MVMNT_DOC_SID as MTRL_MVMNT_DOC_SID,
T1.MATERIAL_SID as MATERIAL_SID,
T1.MVMNT_DOC_CLSS_SID as MVMNT_DOC_CLSS_SID,
T1.PLANT_SID as PLANT_SID,
T1.REVERSE_PLANT_SID as REVERSE_PLANT_SID,
T1.UOM_SID as UOM_SID,
T1.TIME_SID as TIME_SID,
T1.MOVEMENT_QTY as MOVEMENT_QTY,
T1.MOVEMENT_AMTR as MOVEMENT_AMTR
from
Mart...MTRL_MVMNT_DTL_RCV T1,
Mart...MOVEMENT_DOC_CLSS Movement_Document_Class
where
(T1.MVMNT_DOC_CLSS_SID = Movement_Document_Class.MVMNT_DOC_CLSS_SID)
) T3,
Mart...MATERIAL_MVMNT_DOC Material_Movement_Document,
Mart...MATERIAL Material,
Mart...PLANT Reverse_Plant,
Mart...UNIT_OF_MEASURE Unit_of_Measure,
(select
Calendar_Time.TIME_SID as TIME_SID
from
(select * from Mart...ALL_TIME where ALL_TIME.FISCAL_VARIANT_CD = ALL_TIME.DFLT_FSCL_VRNT_CD) Calendar_Time
) Calendar_Time
where
((cast(Calendar_Time.TIME_SID as date) >= DATE '2000-01-01') and (cast(Calendar_Time.TIME_SID as date) <= DATE '2000-01-01')) and
(T3.TIME_SID = Calendar_Time.TIME_SID) and
(T3.MATERIAL_SID = Material.MATERIAL_SID) and
(T3.MTRL_MVMNT_DOC_SID = Material_Movement_Document.MTRL_MVMNT_DOC_SID) and
(T3.PLANT_SID = Plant.PLANT_SID) and
(T3.REVERSE_PLANT_SID = Reverse_Plant.PLANT_SID) and
(T3.UOM_SID = Unit_of_Measure.UOM_SID) and
(T3.MVMNT_DOC_CLSS_SID = Movement_Document_Class.MVMNT_DOC_CLSS_SID)
group by
Plant.PLANT_NM,
Movement_Document_Class.DOCUMENT_TYPE_DSC,
cast(T3.TIME_SID as date),
Material_Movement_Document.USER_NM,
Material_Movement_Document.DOCUMENT_NO,
Material_Movement_Document.ORDER_DOC_ITM_NO,
Material.MATERIAL_ID,
Material.MATERIAL_DSC,
Material.MATERIAL_GRP_DSC,
Reverse_Plant.PLANT_ID,
Reverse_Plant.PLANT_NM,
Unit_of_Measure.UOM_DSC
********************************************************

 

[misscognos]

run that sql against your db. how long does it take to return?