asked
Can anybody suggest to tune this query in betterway as it takes more time in ETL Processing?
select si_dtl.tran_ou, si_dtl.company_code, tran_no, line_no, si_dtl.tran_type, component_id, tran_date, posting_date, tran_qty,item_code, item_variant, uom, si_dtl.supplier_code, drcr_flag, account_code, si_dtl.fb_id, acct_currency, base_amount, case drcr_flag when 'Dr' then tran_amount_acc_cur else - 1 * tran_amount_acc_cur end tran_amount, case drcr_flag when 'Dr' then tran_amount_acc_cur else - 1 * tran_amount_acc_cur end payment, 0 Prepay_amnt, 0 Deposit_amnt,account_code pay, '' pre, '' dep, 'PAYABLES' ctrl_acct_type from ( select tran_ou, company_code, tran_no,line_no,tran_type,component_id,tran_date,posting_date, item_code, item_variant, uom, supplier_code, drcr_flag, account_code, si_dtl.fb_id, acct_currency, base_amount, tran_amount_acc_cur,tran_qty,mig_flag_dlf from siemn_ods_db..si_acct_info_dtl si_dtl (nolock) where posting_date <= getdate() ) si_dtl join siemn_ods_db..supp_ou_suplmain (nolock) supp on tran_ou = supp_ou_ouinstid and supp_ou_supcode =supplier_code and supp_ou_supstatus = 'AC' where exists (select 1 from siemn_ods_db..ard_supplier_account_mst acc (nolock) where si_dtl.account_code = acc.supppay_account and acc.fb_id = si_dtl.fb_id and acc.company_code = si_dtl.company_code ) and ( exists (select 1 from ( select document_no, tran_type, tran_ou from siemn_ods_db..fbp_posted_Trn_dtl (nolock) fbp where fbp.posting_date <= getdate() and fbp.tran_Type in ('PM_EV','PM_MI','PM_PI','PM_PV','PM_REV','PM_RPI','PM_RPV', 'PM_RSA','PM_RSCA','PM_RSDA','PM_RSPV','PM_RSRC','PM_SA','PM_SCA','PM_SDA','PM_SDI', 'PM_SPPV','PM_SPV','PM_SRC','PM_STC','PM_STD','PM_TPV','PM_VCK','PM_SDV','RM_CPV') group by document_no, tran_type, tran_ou ) fbp where fbp.document_no = si_dtl.tran_no and fbp.tran_ou = si_dtl.tran_ou and fbp.tran_type = si_dtl.tran_type ) or isnull(mig_flag_dlf,'') <> '')
Can you post the actual execution plan - this will tell us where and why it runs slowly. Also, how do you define 'better'? e.g. it currently takes XX hours to run, but I need it to run in YY minutes
Without the execution plan, there are only a few obvious worries in there. First is the ISNULL function on the column: isnull(mig_flag_dlf,'') That's going to lead to scans on whatever table that is. Better to make that an OR statement. Tiny issue, but it will add up, you're not using object owners throughout the code. The IN clause could also be an issue. You might be able to substitute that with a temporary table and use a JOIN. Nothing else jumps out as obvious. The overall complexity of the multiple layered nested queries may be an issue. Rethink the structure.
21 People are following this question.
