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]
