Pada post sebelumnya, i3 telah membahas tentang bagaimana mengimplementasikan index untuk mempercepat eksekusi Query dan bagaimana mengimplementasikan Partisi pada tabel dalam database. Kali ini i3 akan memberikan tips-tips optimasi query lebih lanjut.
Performansi adalah parameter yang penting bagi aplikasi apapun, karena tidak ada pengguna yang bersedia kompromi terhadap kinerja sistemnya. Seperti semua sistem terdistribusi menggunakan database untuk mengimplementasikan logika bisnis, performa berbanding lurus dengan jumlah query yang di-tuning dan pengoptimalan PL/SQL di database.
Beberapa teknik optimasi query database yang dapat diterapkan untuk mengembangkan sistem yang lebih baik adalah sebagai berikut:
1. Hindari mismatch tipe data untuk pengindeksan kolom
Kebanyakan orang menggunakan tanda kutip tunggal (dalam kondisi filter) terlepas dari tipe data yang mereka query. Hal Ini membuat oracle melakukan internal typecast ke tipe data yang dibutuhkan.
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| select name,age,city,state from employee where employee_id=’1000′; |
select name,age,city,state from employee where employee_id=1000; |
| Waktu yang dibutuhkan : 2.3 sec | Waktu yang dibutuhkan : 0.3 sec |
2. Hindari fungsi pada kolom yang diindeks
Biasanya, kita melakukan identifikasi kolom yang paling sering di query kemudian dibuat index pada kolom tersebut. Tapi query kita menggunakan fungsi pada kolom yang terindeks. Hal ini akhirnya akan membatalkan tujuan menciptakan indeks pada kolom tersebut.
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| select name,age,city from employee where substr(employee_name,1,3)=’kar’; |
select name,age,city from employee where employee_name like ‘kar%’; |
| Waktu yang dibutuhkan : 2.8 sec | Waktu yang dibutuhkan : 0.3 sec |
Jika kita terpaksa harus mengunakan fungsi pada query tersebut maka kita bisa membuat function based index pada kolom tersebut.
3. Menentukan kondisi pada WHERE bukan pada HAVING
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| select name, count(1) from employee group by name having name=’karthi’; |
select name, count(1) from employee where name=’karthi’ group by name; |
| Waktu yang dibutuhkan = 2.2 sec | Waktu yang dibutuhkan = 0.3 sec |
Ini bukanlah sebuah error. Jika filter dilakukan sebelum pengelompokan, maka semua data yang tidak perlu akan dikelompokan dan akhirnya data yang dibutuhkan akan difilter. Menerapkan filter sebelum pengelompokan akan menghindari sortasi dan pengelompokkan yang tidak perlu.
Baca Juga :
4 Tips SQL*Plus yang Bermanfaat bagi Oracle Developer Pemula
4. Penggunaan join untuk mengganti inner query
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| select employee_name from employee where employee_id in ( select employee_id from defaulters) |
select employee_name from employee e, defaulters d where e.employee_id=d.employee_id |
| Waktu yang dibutuhkan : 14.1 sec | Waktu yang dibutuhkan : 5.5 sec |
Hal ini sebenarnya dianggap sebagai praktek yang buruk pada penulisan SQL, menulis hasil inner query pada tiap-tiap baris hasil query tabel utama.
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| select so.documnet_number count(1) from activation a, serv_ord so, task t where t.documnet_number= so.document_number and so.serv_item_id=a.serv_item_idgroup by so.document_number |
select so.documnet_number count(1) from task t, serv_ord so, activation a, where t.documnet_number= so.document_number and so.serv_item_id=a.serv_item_idgroup by so.document_number |
| Waktu yang dibutuhkan : 10 Sec | Waktu yang dibutuhkan : 2.1 Sec |
5. Menentukan tabel dengan ukuran paling kecil, pada urutan terakhir pada query join.
Seperti yang kita lihat, menggunakan join menghasilkan hasil yang lebih baik daripada inner query. Kita harus mengurutkan tabel sedemikian rupa sehingga tabel terkecil akan ditentukan pada akhir di SQL, sehingga waktu oracle untuk membandingkan baris akan berkurang.
6. Mengganti NOT IN dengan NOT EXISTS
Hal ini sama halnya dengan menghindari subquery
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| Select count(1) from task t where t.document_number not in ( select tt.document_number from task_bkp) |
select count(1) from task t where not exists (select tt.document_number from task_bkp) |
| Waktu yang dibutuhkan : 500 Sec | Waktu yang dibutuhkan : 6 Sec |
7. Menggunkan FORALL sebagai pengganti FOR
Ini adalah salah satu fitur yang berguna, yang tersedia di oracle untuk memasukan bulk record.
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| DECLARE TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER; TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; pnums NumTab; pnames NameTab; BEGIN FOR j IN 1..20000 LOOP — load index-by tables pnums(j) := j; pnames(j) := ‘Part No. ‘ || TO_CHAR(j); END LOOP; FOR i IN 1..20000 LOOP — use FOR loop INSERT INTO parts VALUES (pnums(i), pnames(i)); END LOOP; END; |
DECLARE TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER; TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; pnums NumTab; pnames NameTab; BEGIN FOR j IN 1..20000 LOOP — load index-by tables pnums(j) := j; pnames(j) := ‘Part No. ‘ || TO_CHAR(j); END LOOP; FORALL I in 1 .. 20000 — use FORALL INSERT INTO parts VALUES (pnums(i), pnames(i)); END; |
| Waktu yang dibutuhkan: 11.0 Sec | Waktu yang dibutuhkan: 0.5 sec |
FORALL akan mengurangi waktu pengulangan pada PL/SQL dan SQL.
8. Penggunaan BULK COLLECT
BULK COLLECT adalah suatu fitur yang disediakan oleh Oracle untuk menghindari penggunaan loop dalam pengumpulan data dari table. Untuk aplikasi pengolahan data berat, BULK COLLECT akan sangat berguna. Sebagai contoh, kita perlu memilih 1000 baris dari tabel dan memproses baris dan masukkan ke tabel lain, maka kita dapat menggunakan BULK COLLECT.
| Sebelum Optimasi | Setelah Optimasi |
|---|---|
| Declare Type bcode is table of products.barcode%TYPE; i int; barc bcode; cursor cur_seq is select barcode from products where rownum<100001; begin i:=0; for cur_dta in cur_seq loop i:=i+1; barc:=cur_dta.barcode; end loop; end; |
Declare Type bcode is table of products.barcode%TYPE; i int; barc bcode; begin select barcode BULK COLLECT into barc from products where rownum<100001; end; |
| Waktu yang dibutuhkan : 17sec | Waktu yang dibutuhkan : 1.41 sec |
Ringkasan dari Tips Optimasi Query
- Gunakan kode seragam di seluruh aplikasi standar
- Hindari ketidakcocokan jenis data untuk indeks kolom
- Hindari fungsi pada kolom indeks
- Pindahkan kondisi dari klausa HAVING ke klausa WHERE
- Gunakan joins bukan nested selects, jika memungkinkan
- Mengganti Not IN dengan Not EXISTS atau OUTER JOIN
- Gunakan bulk inserts pada insert banyak records
- Gunakan klausa BULK COLLECT pada fetching records
Baca Juga :
Pernah Mengalami CHECKPOINT NOT COMPLETE Pada Database Oracle? Begini Cara Mengatasinya
Sekian beberapa tips untuk melakukan optimasi database. Di luar tips-tips di atas, tentunya masih banyak lagi hal yang dapat kita lakukan untuk optimasi query database. Pelajari lebih lanjut tentang optimasi query database bersama pelatihan yang diadakan oleh i3.
Untuk info lebih lengkap mengenai training yang tersedia di i3, Anda dapat menghubungi langsung tim sales kami melalui halaman Contact Us.