From: "Murid Belajar Access" <murid.belajaraccess@yahoo.co.id>
To: <belajar-access@yahoogroups.com>
Sent: Sunday, August 09, 2009 3:05 PM
Subject: Re: [belajar-access] Tips menggunakan Query : OPTIMASI SQL
Sekedar menambahkan. Sudah pernah saya kirimkan di milis ini. Saya kirimkan
kembali untuk mengingatkan kembali.
diambil dari : http://www.pcmedia.co.id/detail.asp?Id=2429&Cid=22&Eid=
OPTIMASI SQL
Database administrator dan programer sering menggunakan SQL (Structured
Query Language) untuk memberikan instruksi kepada database. Tetapi
hati-hati, berikan instruksi yang tepat agar database Anda tidak ngambek.
Joko Nurjadi
JIKA DIIBARATKAN manusia, database adalah sahabat yang patuh dan mengerti
pada setiap perintah yang diberikan, sayangnya terkadang tidak berlaku
sebaliknya, kita tidak patuh dan tidak mengerti pada "perintah" yang
diberikan database.
Database kadang dapat "mengomel" dengan berbagai cara, bisa jadi dalam
bentuk performance yang menurun, pesan kesala han, atau bahkan hasil
laporan yang tidak sesuai. Semua-nya dapat kita minimalisasi, bahkan sebelum
hal itu terjadi.
SQL dan RDBMS
Optimasi dapat dilakukan dengan berbagai cara, dengan memahami tuning
performance pada database dan best practice dari berbagai sumber, Anda dapat
memiliki fundamental yang kuat dalam mengoptimalkan kinerja database.
Beberapa teknik dan metoda mungkin memerlukan perlakuan khusus yang berbeda,
tergantung pada database yang Anda gunakan.
Sebagai contoh, peningkatan kinerja bisa dilakukan dari sisi administrasi
database seperti kon? gurasi ?le dan peng-updatean service atau security
pack, yang tentunya masing-masing database memiliki keunikan dan teknik
tersendiri.
Lalu, dengan pertimbangan kompatibilitas, adakah optimasi yang dapat
dilakukan secara umum?
Terdapat seperangkat metode dan teknik yang umum diterapkan saat Anda
bekerja dengan RDBMS (Relational Database Management System), mungkin tidak
semuanya dapat Anda implementasikan karena sangat tergantung pada lingkungan
aplikasi masing-masing, tetapi setidaknya Anda dapat meng-gunakannya sebagai
panduan dan referensi untuk membentuk sistem yang terbaik sesuai dengan
kondisi yang dihadapi.
Optimasi melalui perintah SQL juga memegang peranan yang tidak kalah
penting. Inti dari SQL itu sendiri adalah perintah untuk melakukan
pengambilan (retrieval), penambahan (insertion), modi?kasi (updating), dan
penghapusan (deletion) data, disertai dengan fungsi-fungsi pendukung
administrasi dan managemen database.
SQL sendiri merupakan sebuah bahasa atau pemrograman standar untuk RDBMS.
Walaupun disebut bahasa, mungkin sedikit janggal saat kita menyebut bahasa
pemrograman SQL, lebih familiar jika yang terdengar adalah pemrograman C,
Visual Basic, Java, Delphi, dan seterusnya.
Bahasa-bahasa yang disebut belakangan termasuk dalam pemrograman imperative,
mudahnya adalah bahasa yang berbentuk instruksi-instruksi inti. Sedangkan,
SQL termasuk dalam pemrograman declarative, yang lebih berbentuk kalimat
atau pernyataan.
Dalam pengembangannya, SQL terbagi-bagi lagi dalam berbagai extension
sehingga melahirkan berbagai sebutan seperti SQL/PSM (Persistent Stored
Modules) yang merupakan standar ANSI/ISO, T-SQL (Transact-SQL) dari
Microsoft dan SyBase, PL/SQL (PL merupakan singkatan dari Procedural
Language) yang digunakan oleh Oracle, yang kemudian dikembangkan lagi
menjadi PL/pgSQL yang digunakan PostgreSQL.
Cukup membingungkan, bukan? Untungnya konsep dan elemen-elemen dasar dalam
SQL seperti statement, query, expression, ataupun clause tetap berlaku umum
pada setiap SQL extension.
Kita cukupkan pembahasan teori sampai di sini, berikut adalah beberapa
optimasi sederhana yang dapat Anda lakukan, untuk setidaknya memperbaiki
atau mencegah permasalahan, dan meningkatkan performa RDBMS Anda.
Index
Optimasi pertama yang kita bahas adalah permasalahan index, tentu Anda
mengetahui bahwa index dapat meningkatkan kecepatan pencarian pada record
yang diinginkan. Tetapi, Anda harus cukup selektif dalam memilih ?eld yang
perlu di-index, karena tidak semua ?eld memerlukannya.
Ibaratnya membaca buku, proses pencarian atau scan akan membaca dari awal
hingga akhir halaman. Pada ?eld yang di-index, pencarian dilakukan secara
index scan, atau membaca pada index, tidak langsung pada table yang
bersangkutan.
Sementara pencarian yang dilakukan langsung dengan membaca record demi
record pada table disebut dengan table scan.
Apakah index scan selalu lebih cepat dibandingkan dengan table scan?
Ternyata tidak juga, table scan bisa jadi bekerja lebih cepat saat mengakses
record dalam jumlah relatif kecil, ataupun pada saat aplikasi memang
memerlukan pembacaan table secara keseluruhan.
Sebaliknya dalam mengakses record yang besar pada ?eld tertentu, index scan
dapat mengurangi operasi pembacaan I/O sehingga tidak jarang menghasilkan
kinerja yang lebih cepat.
Sebagai patokan, Anda dapat menentukan index pada ?eld yang sering
digunakan, misalnya ?eld yang sering diakses oleh klausa WHERE, JOIN, ORDER
BY, GROUP BY.
Menentukan Tipe Data
Tipe data merupakan permasalahan yang gampang-gampang susah. Dari sisi daya
tampung, tipe data yang terlalu kecil atau sebaliknya terlalu besar bagi
suatu ?eld, dapat menimbulkan bom waktu yang menimbulkan masalah seiring
dengan pertambahan data yang pesat setiap harinya.
Menentukan tipe data yang tepat memerlukan ketelitian dan analisa yang baik.
Sebagai contoh, kita perlu mengetahui kapan kita menggunakan tipe data char
atau varchar.
Keduanya menampung karakter, bedanya char menyediakan ukuran penyimpanan
yang tetap (? xed-length), sedangkan varchar menyediakan ukuran penyimpanan
sesuai dengan isi data (variable-length).
Patokan umum adalah menggunakan tipe data char jika ? eld tersebut
diperuntukkan untuk data dengan panjang yang konsisten. Misalnya kode pos,
bulan yang terdiri dari dua digit (01 sampai 12), dan seterusnya. Varchar
digunakan jika data yang ingin disimpan memiliki panjang yang bervariasi,
atau gunakan varchar(max) jika ukurannya melebihi 8000 byte.
Jangan Izinkan Allow Null
Jika memungkinkan, kurangi penggunaan ?eld yang memperbolehkan nilai null.
Sebagai gantinya, Anda dapat memberikan nilai default pada ?eld tersebut.
Nilai null kadang rancu dalam intepretasi programer dan dapat mengakibatkan
kesalahan logika pemrograman. Selain itu, ?eld null mengonsumsi byte
tambahan sehingga menambah beban pada query yang mengaksesnya.
Query yang Mudah Terbaca
Karena SQL merupakan bahasa declarative, maka tidak mengherankan jika Anda
membuat query berbentuk kalimat nan panjang walaupun mungkin hanya untuk
keperluan menampilkan satu ?eld!
Jangan biarkan query Anda susah dibaca dan dipahami, kecuali Anda memang
berniat membuat pusing siapapun yang melihat query Anda. Query panjang yang
ditulis dalam 1baris jelas akan menyulitkan modi? kasi dan pemahaman, akan
jauh lebih baik jika Anda menuliskan query dalam format yang mudah dicerna.
Pemilihan huruf besar dan kecil juga dapat mempermudah pembacaan, misalnya
dengan konsisten menuliskan keyword SQL dalam huruf kapital, dan tambahkan
komentar bilamana diperlukan.
Hindari SELECT *
Select mungkin merupakan keyword yang paling sering digunakan, karena itu
optimasi pada perintah SELECT sangat mungkin dapat memperbaiki kinerja
aplikasi secara keseluruhan. \
SELECT * digunakan untuk melakukan query semua ?eld yang terdapat pada
sebuah table, tetapi jika Anda hanya ingin memproses ?eld tertentu, maka
sebaiknya Anda menuliskan ?eld yang ingin diakses saja, sehingga query Anda
menjadi SELECT ?eld1, ?eld2, ?eld3 dan seterusnya (jangan pedulikan kode
program yang menjadi lebih panjang!). Hal ini akan mengurangi beban lalu
lintas jaringan dan lock pada table, terutama jika table tersebut memiliki
banyak ?eld dan berukuran besar.
Batasi ORDER BY
Penggunaan ORDER BY yang berfungsi untuk mengurutkan data, ternyata
memiliki konsekuensi menambah beban query, karena akan menambah satu proses
lagi, yaitu proses sort.
Karena itu gunakan ORDER BY hanya jika benar-benar dibutuhkan oleh aplikasi
Anda.
Atau jika dimungkinkan, Anda dapat melakukan pengurutan pada sisi client dan
tidak pada sisi server. Misalnya dengan menampung data terlebih dahulu pada
komponen grid dan melakukan sortir pada grid tersebut sesuai kebutuhan
pengguna.
Subquery Atau JOIN
Adakalanya sebuah instruksi dapat dituliskan dalam bentuk subquery atau
perintah JOIN, disarankan Anda memprioritaskan penggunaan JOIN karena dalam
kasus yang umum akan menghasilkan performa yang lebih cepat.
Walaupun demikian, mengolah query merupakan suatu seni, selalu ada
kemungkinan ternyata subquery bekerja lebih cepat dibandingkan JOIN,
misalnya dalam kondisi penggunaan
JOIN yang terlalu banyak, ataupun logika query yang belum optimal.
Gunakan WHERE dalam SELECT
"Di mana ada gula di sana ada semut". Untuk programer database, pepatah itu
perlu dimodi? kasi menjadi "di mana ada SELECT di sana ada WHERE", untuk
mengingatkan pentingnya klausa WHERE sebagai kondisi untuk menyaring record
sehingga meminimalkan beban jaringan.
Saat sebuah table dengan jumlah data yang sangat besar diproses, juga
terjadi proses lock terhadap table tersebut sehingga menyulitkan pengaksesan
table yang bersangkutan oleh pengguna yang lain.
Bahkan jika Anda bermaksud memanggil seluruh record, tetap menggunakan WHERE
merupakan kebiasaan yang baik.
Jika Anda telah menggunakan WHERE pada awal query, maka kapanpun Anda ingin
menambahkan kondisi tertentu, Anda tinggal menyambung query tersebut dengan
klausa AND diikuti kondisi yang diinginkan.
Tapi bagaimana menggunakan WHERE jika benar-benar tidak ada kondisi apapun?
Anda dapat menuliskan suatu kondisi yang pasti bernilai true, misalnya
SELECT .... WHERE 1=1. Bahkan tools open source phpMyAdmin yang berfungsi
untuk mena ngani database MySQL selalu menyertakan default klausa WHERE 1
pada perintah SELECT, di mana angka 1 pada MySQL berarti nilai true.
Kecepatan Akses Operator
WHERE 1=1 dan WHERE 0 <> 1 sama-sama merupakan kondisi yang menghasilkan
nilai true. Tetapi, dalam hal ini lebih baik Anda menggunakan WHERE 1=1
daripada WHERE 0 <> 1. Hal ini dikarenakan operator = diproses lebih cepat
dibandingkan dengan operator <>.
Dari sisi kinerja, urutan operator yang diproses paling cepat adalah:
1. =
2. >, >=, <. <=
3. LIKE
4. <>
Tidak dalam setiap kondisi operator dapat disubtitusikan seperti contoh
sederhana di atas, tetapi prioritaskanlah penggunaan operator yang tercepat.
Membatasi Jumlah Record
Bayangkan Anda menampilkan isi sebuah table dengan menggunakan SELECT, dan
ternyata table tersebut memiliki jutaan record yang sangat tidak diharapkan
untuk tampil seluruhnya.
Skenario yang lebih buruk masih dapat terjadi, yaitu query tersebut diakses
oleh ratusan pengguna lain dalam waktu bersamaan!
Untuk itu, Anda perlu membatasi jumlah record yang berpotensi mengembalikan
record dalam jumlah besar (kecuali memang benar-benar dibutuhkan), pada SQL
Server, Anda dapat menggunakan operator TOP di dalam perintah SELECT.
Contohnya SELECT TOP 100 nama... akan menampilkan 100 record teratas ?eld
nama.
Jika menggunakan MySQL, Anda dapat menggunakan LIMIT untuk keperluan yang
sama.
Batasi Penggunaan Function
Gunakan fungsi-fungsi yang disediakan SQL seperlunya saja.
Sebagai contoh, jika Anda menemukan query sebagai berikut: SELECT nama FROM
tbl_teman WHERE ucase(nama) = 'ABC', nampak query tersebut ingin mencari
record yang memiliki data berisi "abc", fungsi ucase digunakan untuk
mengubah isi ?eld nama menjadi huruf besar dan dibandingkan dengan konstanta
"ABC" untuk meyakinkan bahwa semua data "abc" akan tampil, walaupun
dituliskan dengan huruf kecil, besar, ataupun kombinasinya.
Tetapi, cobalah mengganti query tersebut menjadi SELECT nama FROM tbl_teman
WHERE nama = 'ABC', perhatikan query ini tidak menggunakan function ucase.
Apakah menghasilkan result yang sama dengan query pertama? Jika pengaturan
database Anda tidak case-sensitive (dan umumnya secara default memang tidak
case-sensitive), maka hasil kedua query tersebut adalah sama. Artinya, dalam
kasus ini Anda sebenarnya tidak perlu menggunakan function ucase!
Baca dari Kiri ke Kanan
Query yang Anda tulis akan diproses dari kiri ke kanan, misalkan terdapat
query WHERE kondisi1 AND kondisi2 AND kondisi3, maka kondisi1 akan terlebih
dahulu dievaluasi, lalu kemudian kondisi2, kondisi3, dan seterusnya.
Tentunya dengan asumsi tidak ada kondisi yang diprioritaskan/dikelompokkan
dengan menggunakan tanda kurung.
Logika operator AND akan langsung menghasilkan nilai false saat ditemukan
salah satu kondisi false, maka letakkan kondisi yang paling mungkin memiliki
nilai false pada posisi paling kiri. Hal ini dimaksudkan agar SQL tidak
perlu lagi mengevaluasi kondisi berikutnya saat menemukan salah satu kondisi
telah bernilai false.
Jika Anda bingung memilih kondisi mana yang layak menempati posisi terkiri
karena kemungkinan falsenya sama atau tidak bisa diprediksi, pilih kondisi
yang lebih sederhana untuk diproses.
Gambar dalam Database
Database memang tidak hanya diperuntukkan sebagai penyimpanan teks saja,
tetapi dapat juga berupa gambar. Kalau pepatah mengatakan sebuah gambar
bermakna sejuta kata, tidak berarti kita harus menyediakan tempat
penyimpanan seukuran sejuta kata untuk menampung satu gambar! Akan lebih
baik bagi kinerja database jika Anda hanya menyimpan link ataulokasi gambar
di dalam database, dibandingkan menyimpan ?sik gambar tersebut.
Kecuali jika Anda tidak memiliki pilihan lain, misalnya karena alasan
keamanan atau tidak tersedianya tempat penyimpanan lain untuk gambar Anda
selain di dalam database.
Tetapi, jelas jika Anda dapat memisahkan gambar secara ?sik dari database,
maka ukuran dan beban database akan relatif berkurang drastis, proses
seperti back-up dan migrasi akan lebih mudah dilakukan.
Pengukuran Kinerja
Terdapat tools optimizer yang bervariasi untuk tiap RDBMS, Anda dapat
menggunakannya sebagai panduan untuk meningkatkan kinerja query, di mana
Anda dapat mengetahui berapa lama waktu eksekusi atau operasi apa saja yang
dilakukan sebuah query.
Jika Anda menemukan sebuah query tampak tidak optimal, berusahalah menulis
ulang query tersebut dengan teknik dan metode yang lebih baik. Semakin
banyak query yang dapat dioptimasi, akan semakin baik kinerja aplikasi Anda.
Terutama saat frekuensi pemakaian query tersebut relatif tinggi.
Back-up
Buatlah back-up otomatis secara periodik, sebaiknya tes dan simulasikan
prosedur restore database dan perhitungkan waktu yang diperlukan untuk
membuat sistem pulih kembali jika terjadi sesuatu yang tidak diharapkan pada
database.
Lakukan proses back-up pada waktu di mana aktivitas relatif rendah agar
tidak mengganggu kegiatan operasional.
Banyak Jalan Menuju Roma
Berikan satu masalah pada beberapa programer, maka Anda mungkin akan
mendapatkan beberapa solusi yang berbedabeda. Banyak alternatif yang dapat
diciptakan untuk menghasilkan sesuatu, tetapi tentunya kita menginginkan
alternatif yang terbaik.
Karena itu, jangan ragu mencoba menuliskan ulang query Anda dengan cara lain
jika Anda melihat kemungkinan peningkatan kinerja, contohnya pada potongan
query berikut:
WHERE SUBSTRING(nama,1,1) ='b'
Query di atas akan mengambil record dengan kondisi karakter pertama kolom
nama adalah "b", sehingga akan tampil isi record seperti "Budi", "Badu",
"Benny" dan seterusnya. Cara lain untuk menghasilkan record yang sama adalah
sebagai berikut:
WHERE nama LIKE 'b%'
Hasil yang ditampilkan kedua query tersebut akan sama, tetapi performa yang
dihasilkan (terutama untuk record berukuran besar) akan berbeda. Umumnya
kondisi LIKE akan bekerja dengan lebih cepat dibandingkan function
SUBSTRING.
Contoh lain yang lebih kompleks adalah seperti query beri-kut:
SELECT NIP, nama FROM tbl_pegawai WHERE dept = 'IT' OR kota
= 'jakarta' OR divisi = 'programer'
Perhatikan query di atas memiliki tiga kondisi yang dipisahkan oleh klausa
OR. Alternatif lain adalah dengan menuliskan query sebagai berikut:
SELECT NIP, nama FROM tbl_pegawai WHERE dept = 'IT'
UNION ALL
SELECT NIP, nama FROM tbl_pegawai WHERE kota = 'jakarta'
UNION ALL
SELECT NIP, nama FROM tbl_pegawai WHERE divisi = 'programer'
Walaupun penulisan query menjadi lebih panjang, bisa jadi al-ternatif ini
akan lebih baik. Mengapa? Dengan asumsi ?eld dept memiliki index, sementara
?eld kota dan divisi tidak diindex, query pertama tidak akan menggunakan
index dan melakukan table scan. Berbeda dengan query kedua, index akan tetap
dilakukan pada sebagian query sehingga akan menghasilkan kinerja yang
relatif lebih baik.
Ah... Beda Tipis Saja!
Pastinya masih banyak terdapat teknik lain yang tidak akan dapat dibahas
semuanya dalam artikel ini. Di antara (atau mungkin semua) teknik optimasi
yang dibahas di atas, mungkin Anda akan menemukan bahwa setelah diuji dengan
data sampel maka kinerja sebelum dan sesudah optimasi ternyata sama sekali
tidak signi?kan, beda tipis, atau tidak ada bedanya sama sekali!
Memang benar, dengan spesi? kasi hardware yang semakin meningkat, data yang
relatif kecil, dan alur yang sederhana, Anda mungkin tidak akan mendapatkan
perbedaan yang signi?kan.
Tetapi jika Anda siap untuk terjun menghadapi tantangan menangani aplikasi
yang lebih besar, maka perbedaan antara tanpa dan dengan optimasi akan
sangat nyata, dengan pema-haman dan kebiasaan coding yang baik, Anda akan
dapat menghasilkan aplikasi yang juga lebih baik.
Tidak ada salahnya menerapkan optimasi yang Anda ketahui sedini mungkin
dalam pengembangan sistem aplikasi Anda.
Bahkan jika sebuah aplikasi tnampaknya memiliki kinerja yang cukup baik,
tidak berarti lepas dari usaha optimasi lebih lanjut.
Terutama jika Anda mengharapkan aplikasi tersebut mampu berkembang lebih
jauh, tidak pernah ada kata sempurna bagi suatu sistem aplikasi, tetapi
setiap sistem selalu ada kesempatan menjadi lebih berguna. Salah satunya
dengan selalu mencari cara yang lebih baik
LEBIH LANJUT
· http://blog.sqlauthority.com/
No comments:
Post a Comment