Home » RDBMS Server » Performance Tuning » suggest
suggest [message #152304] Thu, 22 December 2005 07:31
sethybibeka
Messages: 10
Registered: August 2005
Location: bhubanes
Junior Member
This query taking 40 mins plz suggest how to reduce time

SELECT (AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING) as DATEOFUPLOADING,
sum(AINVVOUCHERHISTORY.INVOICEAMOUNT) as amount,
count(distinct(AINVVOUCHERDETAILS.AINVVOUCHERDETAILID)) AS INVOICES

FROM
INVOICE.MDF_AINVTHERAAREA,
INVOICE.MDF_AINVDEPARTMENTS,
INVOICE.MDF_AINVTHERAAREADEPARTMENTS,
INVOICE.AINVVOUCHERDETAILS,
INVOICE.AINVVOUCHERHISTORY,
INVOICE.AINVVOUCHERDATEHISTORY,
ainvapproverhistorydetails
WHERE
INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID=INVOICE.AINVVOUCHERHISTORY.ANVVOUCHERDETAILID and
( INVOICE.AINVVOUCHERDETAILS.AINVVOUCHERDETAILID= INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID )
and AINVVoucherHistory.ANVVoucherDetailID=ainvapproverhistorydetails.AINVVOUCHERDETAILID
AND ( INVOICE.MDF_AINVDEPARTMENTS.AINVDEPARTMENTID=INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVDEPARTMENTID )
AND ( INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVTHERAAREAID=INVOICE.MDF_AINVTHERAAREA.AINVTHERAAREAID )
AND ( INVOICE.AINVVOUCHERHISTORY.DATEOFUPLOADING >= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING AND INVOICE.AINVVOUCHERHISTORY.ENTEREDDATE <= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING )
AND ( INVOICE.AINVVOUCHERHISTORY.DEPARTMENT=INVOICE.MDF_AINVDEPARTMENTS.DEPARTMENT )
and ((ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING-INVOICE.AINVVOUCHERDETAILS.DUEDATE)>=90)

and to_char(ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING,'dd')='10'
and AINVVOUCHERHISTORY.INVOICEAMOUNT>=0
and ainvapproverhistorydetails.PROJECTAPPROVER NOT IN('**NOT ACTIVE IN WF') and
ainvapproverhistorydetails.EXITDATE is Null
GROUP BY (ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING)







For 30-59

SELECT (AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING) as DATEOFUPLOADING,
sum(AINVVOUCHERHISTORY.INVOICEAMOUNT) as amount,
count(distinct(AINVVOUCHERDETAILS.AINVVOUCHERDETAILID)) AS INVOICES

FROM
INVOICE.MDF_AINVTHERAAREA,
INVOICE.MDF_AINVDEPARTMENTS,
INVOICE.MDF_AINVTHERAAREADEPARTMENTS,
INVOICE.AINVVOUCHERDETAILS,
INVOICE.AINVVOUCHERHISTORY,
INVOICE.AINVVOUCHERDATEHISTORY,
ainvapproverhistorydetails
WHERE
INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID=INVOICE.AINVVOUCHERHISTORY.ANVVOUCHERDETAILID and
( INVOICE.AINVVOUCHERDETAILS.AINVVOUCHERDETAILID= INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID )
and AINVVoucherHistory.ANVVoucherDetailID=ainvapproverhistorydetails.AINVVOUCHERDETAILID
AND ( INVOICE.MDF_AINVDEPARTMENTS.AINVDEPARTMENTID=INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVDEPARTMENTID )
AND ( INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVTHERAAREAID=INVOICE.MDF_AINVTHERAAREA.AINVTHERAAREAID )
AND ( INVOICE.AINVVOUCHERHISTORY.DATEOFUPLOADING >= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING AND INVOICE.AINVVOUCHERHISTORY.ENTEREDDATE <= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING )
AND ( INVOICE.AINVVOUCHERHISTORY.DEPARTMENT=INVOICE.MDF_AINVDEPARTMENTS.DEPARTMENT )
and ((ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING-INVOICE.AINVVOUCHERDETAILS.DUEDATE)>=30) and
((ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING-INVOICE.AINVVOUCHERDETAILS.DUEDATE) < 60)
and to_char(ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING,'dd')='10'
and AINVVOUCHERHISTORY.INVOICEAMOUNT>=0

and ainvapproverhistorydetails.PROJECTAPPROVER NOT IN('**NOT ACTIVE IN WF') and
ainvapproverhistorydetails.EXITDATE is Null
GROUP BY (ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING)



Previous Topic: Analyzing indexes...
Next Topic: Tuning of Select for a View in Report
Goto Forum:
  


Current Time: Mon Nov 28 04:58:07 CST 2022