2

The query below runs quickly when the "FROM" clause has only a single account number (a.account_nbr) and a single object code (a.fin_object_cd), but when I modify the query so that the FROM clause has a range of account numbers and a range of object codes, it takes a VERY long amount of time to return results. It goes from about a minute to run to 20 or more minutes.

The query does return the results I want, but I need to make it run more quickly.

What can I do? I am not sure if adding indexes to some columns would help, or if there's just a better way to write the query.

If you need more information about the tables, or what I'm trying to accomplish, please let me know.

select 
a.account_nbr as "Account Number", 
a.account_nm as "Account Name", 
a.fin_object_cd as "Object Code", 
a.fin_obj_cd_nm as "Object Code Name", 

(select COALESCE(sum(fin_beg_bal_ln_amt),0) from kfsprd.gl_balance_t where account_nbr = a.account_nbr and fin_object_cd = a.fin_object_cd and univ_fiscal_yr = "2015" and fin_balance_typ_cd != "CB") as "Beginning Balance",

(select COALESCE(sum(trn_ldgr_entr_amt),0) FROM kfsprd.kf_f_transaction_dtl where univ_fiscal_yr = "2015" and account_nbr = a.account_nbr and fin_object_cd = a.fin_object_cd and trn_debit_crdt_cd = "D") as "Debits",
(select COALESCE(sum(trn_ldgr_entr_amt),0) FROM kfsprd.kf_f_transaction_dtl where univ_fiscal_yr = "2015" and account_nbr = a.account_nbr and fin_object_cd = a.fin_object_cd and trn_debit_crdt_cd = "C" and trn_ldgr_entr_amt is not null) as "Credits",

(
(select COALESCE(sum(fin_beg_bal_ln_amt),0) from kfsprd.gl_balance_t where account_nbr = a.account_nbr and fin_object_cd = a.fin_object_cd and univ_fiscal_yr = "2015" and fin_balance_typ_cd != "CB") +
(select COALESCE(sum(trn_ldgr_entr_amt),0) FROM kfsprd.kf_f_transaction_dtl where univ_fiscal_yr = "2015" and account_nbr = a.account_nbr and fin_object_cd = a.fin_object_cd and trn_debit_crdt_cd = "D") -
(select COALESCE(sum(trn_ldgr_entr_amt),0) FROM kfsprd.kf_f_transaction_dtl where univ_fiscal_yr = "2015" and account_nbr = a.account_nbr and fin_object_cd = a.fin_object_cd and trn_debit_crdt_cd = "C") 
) as "Ending Balance"

from kfsprd.kf_f_transaction_dtl a where univ_fiscal_yr = "2015" and (univ_fiscal_prd_cd between "01" and "13" or 
univ_fiscal_prd_cd = "BB") and a.account_nbr between "1014000" and "1014005" and a.fin_object_cd between "0000" and "9999" and a.fin_balance_typ_cd != "CB"
group by account_nbr, fin_object_cd
order by account_nbr, fin_object_cd;
Doug Davis
  • 65
  • 6
  • As [pointed it](http://stackoverflow.com/questions/17354219/how-to-speed-up-sql-queries-indexes), you may want to create an index on columns that' are used in a where condition, unless it's a double or a varchar. Also, [explain](http://dev.mysql.com/doc/refman/5.5/en/using-explain.html) might give you some idea of where you're going wrong. – Jonast92 Apr 30 '15 at 18:57
  • A minute doesn't seem quick to me, but we know nothing about your dataset! If it was me, I'd start again, with proper DDLs (and/or an sqlfiddle) TOGETHER WITH the desired result. – Strawberry May 03 '15 at 15:35
  • 1
    Thank you both for your help! I received a response from someone who is familiar with the structure of the database I'm querying against, and his suggestion helped immensely! – Doug Davis May 04 '15 at 19:08

1 Answers1

4

Are you able to add fin_coa_cd to the where clause? If that has the same primary key as the original table (gl_entry_t), then the first 3 columns of that table would be univ_fiscal_yr, fin_coa_cd, and account_nbr. If you have a chart code to use, it may help Oracle use that index in the main query.

Jonathan
  • 141
  • 2
  • 2
    Wow! Jonathan! You are the man! I added the fin_coa_cd field to the where clause of not just the main query, but to the six sub-queries, as well, and the query that previously took 27 minutes now only takes about 10 seconds! Thank you, so much! – Doug Davis May 04 '15 at 19:07