-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: AnithaNaidu9
Message 1 in Discussion
Hi All,
Please help me in tuning the below query which is taking 15 mins. to execute
in Production environment and 'Acc_trans_detail table is having millions of
record and has 15 indexed column.
Please suggest me how i can tune the below query.
Thanks in advance :).
select distinct
acc.AR_AP_SEQ_ID,
TO_CHAR(acc.DUE_DATE,'MM/DD/YYYY') AS DUE_DATE,
(SELECT SOURCE_ENTITY_TYPE
FROM ACC_TRANS_DETAIL
WHERE TRANS_ID = acc.PREV_TRANS_ID) AS ORIG_ENTITY_TYPE,
(SELECT SOURCE_ENTITY_ID
FROM ACC_TRANS_DETAIL
WHERE TRANS_ID = acc.PREV_TRANS_ID) AS ORIG_ENTITY_ID,
acc.TRANS_AMOUNT,
acc.TRANS_ID,
acc.TRANS_TIME_STAMP,
ACC.TRANS_STATUS,
arc.CHECK_PRINT_DATE,
arc.CHECK_NUMBER,
acc.OFFICE_CODE,
(SELECT USER_ID
FROM ACC_TRANS_HISTORY
WHERE RECORD_ID = (SELECT MIN(RECORD_ID)
FROM ACC_TRANS_HISTORY
WHERE TRANS_ID = acc.TRANS_ID AND
DUE_DATE = acc.DUE_DATE) )
AS USER_ID
FROM acc_refund_check_trans arct,
acc_refund_check arc,
acc_trans_detail acc
WHERE acc.trans_status = 'PENDING REFUND' AND
acc.ar_ap_seq_id is not null and
arct.trans_id IN (SELECT trans_id
FROM acc_trans_detail
WHERE ar_ap_seq_id = acc.ar_ap_seq_id AND
trans_status = 'OK' AND
form_of_trans = 'CHK') AND
arct.check_id = arc.check_id AND
arc.check_status <> 'VOIDED';
_________________________________________________________________
Catch the complete World Cup coverage with MSN
http://content.msn.co.in/Sports/Cricket/Default.aspx
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
