Maaf linknya rusak ini querynya: http://docs.google.com/View?id=df9tz3kc_50cbnx97ht
output query planner: http://docs.google.com/View?id=df9tz3kc_47hkpcjwfr dan tabel2 yg digunakan: http://docs.google.com/View?id=df9tz3kc_46g8pcsqdn saat ini saya masih mencoba merubah query tersebut kedalam function --- In [email protected], "Aal-Rafizeldi" <rafize...@...> wrote: > > Dear all.. > Maaf sebelumnya, sebenarnya saya pakai postgresql tapi saya belum ketemu > komunitasnya. > Saya dapat tugas untuk mentuning query, dan saya benar2 baru > menghadapi sql sebanyak ini, > saya mohon bantuan & saran2 dari senior2 sekalian > > eksekusinya butuh waktu +-50 detik > saya tidak punya akses ke server > tabel skala_rental memiliki +2jt baris, > tabel cf_application & personal_main memiliki 100-200 ribuan baris > didalam file ini hasil query plannya queryplan.txt > <http://f1.grp.yahoofs.com/v1/gDPUSgXg77QE4witcOu9K1QeqrcyQIggAeaiRE6vut\ > ne7ZwHeGtTv09UJoJUHLeYwxWqB3tcB-1jo44lOmgvJmlueDCa8hE/queryplan-mri> > dan ini struktur tabel yg terkait table.txt > <http://f1.grp.yahoofs.com/v1/kEHUStg0LAu_jmD6JHF2oSqwn9taEhxjkWpQB-BY7W\ > EZgYMxpk6nH8jVTnqazSoecJXhgAPVLQGv9sWMNm72OCRNca6HIMk/table-mri> > > 1. Menurut teman2 sekalian apakah query dibawah ini sudah optimal jika > dilihat dari penulisannya?? > 2. Jika belum, bagaimana cara mengoptimalkannya?? > querynya: > > select > cf.cf_application_id as Id490_0_, > '' as CfApplic2_490_0_, > cf.cf_drawdown_number as Drawdown3_490_0_, > '' as OldDrawd4_490_0_, > p.full_name as Personal5_490_0_, > c.name as Corporat6_490_0_, > coalesce( (select name > from lookup > where lookup_id = pe.employment_status_id), 'NON GROUP' > ) as Group490_0_, > > --principle +1 tahun, 2009/1/1 > coalesce( (select sum(principle) > from skala_rental > where cf_application_id = cf.cf_application_id > and (payment_date is null or payment_date > '2008/1/1') > and counter <> 0 > and due_date <= dateadd('year', 1, '2008/1/1')), 0 > ) as Principle8_490_0_, > --interest +1 tahun, 2009/1/1 > coalesce( (select sum(interest) > from skala_rental > where cf_application_id = cf.cf_application_id > and (payment_date is null or payment_date > '2008/1/1') > and counter <> 0 > and due_date <= dateadd('year', 1, '2008/1/1')), 0 > ) as Interest12_490_0_, > > --principle (2009/1/1 + 1hari) - 2010/1/1 > coalesce( (select sum(principle) > from skala_rental > where cf_application_id = cf.cf_application_id > and due_date between dateadd('day', 1, dateadd('year', 1, > '2008/1/1')) > and dateadd('year', 2, '2008/1/1')), 0 > ) as Principle9_490_0_, > --interest (2009/1/1 + 1hari) - 2010/1/1 > coalesce( (select sum(interest) > from skala_rental > where cf_application_id = cf.cf_application_id > and due_date between dateadd('day', 1, dateadd('year', 1, > '2008/1/1')) > and dateadd('year', 2, '2008/1/1')), 0 > ) as Interest13_490_0_, > > --principle (2010/1/1 + 1hari) - 2011/1/1 > coalesce( (select sum(principle) > from skala_rental > where cf_application_id = cf.cf_application_id > and due_date between dateadd('day', 1, dateadd('year', 2, > '2008/1/1')) > and dateadd('year', 3, '2008/1/1')), 0 > ) as Principle10_490_0_, > --interest (2010/1/1 + 1hari) - 2011/1/1 > coalesce( (select sum(interest) > from skala_rental > where cf_application_id = cf.cf_application_id > and due_date between dateadd('day', 1, dateadd('year', 2, > '2008/1/1')) > and dateadd('year', 3, '2008/1/1')), 0 > ) as Interest14_490_0_, > > --principle > (2008 + 3) > coalesce( (select sum(principle) > from skala_rental > where cf_application_id = cf.cf_application_id > and due_date > dateadd('year', 3, '2008/1/1')), 0 > ) as Principle11_490_0_, > --interest > (2008 + 3) > coalesce( (select sum(interest) > from skala_rental > where cf_application_id = cf.cf_application_id > and due_date > dateadd('year', 3, '2008/1/1')), 0 > ) as Interest15_490_0_, > > coalesce( (sr.unearned + sr.interest), 0) as Unearne16_490_0_, > coalesce(sr.outstanding_balance, 0) as OsGross490_0_, > (select number_of_digit > from organization_setup > where organization_id = cf.organization_id > ) as NumberO18_490_0_ > > from cf_application cf > left join personal_main p on p.personal_id = cf.personal_id > left join corporate_main c on c.corporate_id = cf.corporate_id > left join personal_employment pe on pe.personal_id = p.personal_id > join skala_rental sr on sr.cf_application_id = cf.cf_application_id > > where 1=1 > and cf.drawdown_date <= '2008/1/1' > and (cf.terminate_date is null or cf.terminate_date > '2008/1/1') > and sr.counter = (select min(counter) > from skala_rental > where cf_application_id = cf.cf_application_id > and (payment_date is null or payment_date > > '2008/1/1') > and counter <> 0) > and cf.branch_id = 2 > order by cf.cf_drawdown_number > > > > > > > [Non-text portions of this message have been removed] >
