Beberapa teknik atau tips optimasi query SQL biar akses lebih cepat yang dapat diterapkan untuk membuat sistem yang lebih baik dan lebih efisien.
Karena jika tidak dioptimasi maka jika yang diakses adalah data besar maka akan membuat program lemot karena masih menunggu aksesing query yang salangat lambat, maka dari itu sebaiknya optimalkan query SQL database Anda dengan cara-cara berikut ini :
1# Kurangi join table, gunakan partial query
Yaitu membuat
query utama sesederhana mungkin supaya waktu prosesnya cepat. Jika diperlukan informasi tambahan dari tabel lain bisa digunakan query kecil untuk mengambil data tambahan tersebut.
Manfaatnya adalah supaya dapat membagi waktu proses menjadi beberapa query sehingga resources server dapat diselipi oleh request dari user lain.
2# Urutkan tabel join
Jika terpaksa dan harus menggunakan join, maka urutan penyebutan tabel yang di-join harus dimulai dari tabel yang memiliki jumlah data terbesar terlebih dahulu, baru kemudian diikuti yang datanya lebih sedikit.
Namun harus tetap diusahakan agar join tabel sedikit. Jika memungkinkan bisa dibuat partial query seperti poin 1.
3# Gunakan force index
Jika ada join tabel yang jumlahnya lebih dari 3 tabel, mungkin MySQL akan bingung menggunakan index yang mana. Jika MySQL tidak dapat menentukan index yang akan dipakai, maka MySQL akan mem-
parsing semua data. Ini artinya sangat memboroskan
resources server.
Jika
query-nya memang harus begitu, maka kita perlu membantu MySQL menentukan index yang dipakai. Gunakan perintah
force index untuk memaksa MySQL menggunakan index tertentu.
Jika index yang dimaksud belum dibuat, sebaiknya dibuat terlebih dahulu. Performa query yang menggunakan index akan sangat baik performanya.
4# Batasi jumlah data dalam query
Jika suatu tabel memiliki jumlah data yang sangat besar, sebaiknya kita tidak perlu mengambil kesemua data. Gunakan teknik pagination yang akan mengambil data per halaman. Misalnya sekali tampilan per halaman ada 10 data, maka kita hanya mengambil 10 data saja.
Teknik pagination ini telah diakomodasi dengan baik oleh MySQL dengan perintah
limit. Gunakan perintah ini dengan baik.
5# Buat index
Buat index pada tabel yang sering digunakan dengan urutan tertentu atau di-group dengan acuan field tertentu. Atau jika sering di-join dengan tabel lain.
Gunakan pula tipe field yang relatif lebih cepat, misalnya gantikan tipe varchar dengan char(n) yang akan lebih cepat indexing-nya. Perhatikan pula encoding-nya. Gunakan encoding
UTF-8 yang lebih cepat.
6# Gunakan library PHP terbaru (untuk Pemrograman PHP)
Pada PHP gunakan kelas
mysqli_query yang lebih baru. Jangan gunakan lagi library
mysql_query yang tidak lagi diteruskan pengembangannya.
7# Gunakan mysqli_fetch_assoc (untuk Pemrograman PHP)
Ternyata kita perlu memilih fungsi yang lebih efisien. Misalnya gunakan
mysqli_fetch_assoc dari pada
mysqli_fetch_array. Walau pun cara penggunaannya sama dan bisa digunakan untuk hasil yang sama, namun mysql_fetch_array lebih boros dalam struktur datanya.
8# Test query sebelum dipakai dalam program
Nah ini termasuk sangat penting. Kita perlu mengetest query yang kita buat sebelum digunakan dalam program. Test query bisa menggunakan phpmyadmin, tora, emma, MySQL workbench, dll. Pada utility2 ini telah tersedia fasilitas untuk mengetahui berapa waktu yang digunakan mysql untuk memproses query. Jika query membutuhkan waktu lebih dari 1 detik, maka boleh dibilang query-nya perlu dioptimasi.
9# 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 |
10# 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.
11# 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 dikelompokkan dan akhirnya data yang dibutuhkan akan difilter. Menerapkan filter sebelum pengelompokan akan menghindari sortasi dan pengelompokkan yang tidak perlu.
12# 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 |
13# 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.
14# 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 |
15# Menggunkan FORALL sebagai pengganti FOR
Ini adalah salah satu fitur yang berguna, yang tersedia di oracle untuk memasukkan 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.
16# Penggunaan BULK COLLECT
BULK COLLECT adalah suatu fitur yang disediakan oleh DBMS untuk menghindari penggunaan loop dalam pengumpulan data dari table. Untuk aplikasi pengolahan data berat, BULK COLLECT akan 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 |
17# Hindari Penggunaan select * from ...
Hindari penggunaan Query select * from, sebaiknya sebutkan nama field yang akan Anda tampilkan misal select nama, gender, from .... karena dengan penyebutan field database dengan mudah mengurutkan apa yang akan ditampilkan sebailknya jika menggunakan * maka semua field dieksekusi bersamaan dan inilah yang membuat akses data melambat karena tidak ada antrian alias berdesakan.
Demikian beberapa catatan hasil pertemuan dengan seorang hacker setahun yang lalu. Tentu saja catatan ini masih bisa diperdebatkan. Atau mungkin Anda ada masukan tambahan? Semoga bermanfaat.
