Optimasi Query

Setiap database yang tersimpan pastinya memiliki kapasitas data yang  semakin besar ukuran maupun volumenya.  Apabila tidak didukung dengan kecepatan akses yang memadai menyebabkan pengiriman data yang tersendat.  Oleh sebab itu,diperlukan cara untuk meminimalisir terjadinya hal itu yaitu dengan adanya optimasi query serta faktor-faktor lain yang mempengaruhi tersendatnya data yang terkirim  agar tercipta kinerja yang maksimal.

Optimasi query merupakan suatu proses untuk menganalisis dan menentukan sumber apa saja yang digunakan oleh query tersebut dan apakah penggunaan dari sumber tersebut dapat dikurangi tanpa merubah output. Tujuan dari optimasi query ini adalah untuk menentukan jalan akses yang temudah untuk meminimalisir waktu agar tidak menyebabkan seluruh performa aplikasi menjadi menurun, melainkan bagaimana bisa bekerja dengan terus meningkat.

Optimasi query dapat dilakukan dengan berbagai cara, dengan memahami tuning performance pada database dan best practice dari berbagai sumber. Beberapa teknik dan metoda mungkin memerlukan perlakuan khusus yang berbeda, tergantung pada database yang kita gunakan.

Sebagai contoh, peningkatan kinerja bisa dilakukan dari sisi administrasi database seperti konfigurasi  dan peng-update-an  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 kita bekerja dengan RDBMS (Relational Database Management System), mungkin tidak semuanya dapat diimplementasikan karena sangat tergantung pada lingkungan aplikasi masing-masing, tetapi setidaknya kita dapat meng-gunakannya sebagai panduan dan referensi untuk membentuk sistem yang terbaik sesuai dengan kondisi yang dihadapi.

Optimasi Pada Perintah SQL

Optimasi melalui perintah SQL juga memegang peranan yang tidak kalah penting. Inti dari SQL itu sendiri adalah perintah untuk melakukan pengambilan (retrieval), penambahan (insertion), modifikasi (updating), dan penghapusan (deletion) data, disertai dengan fungsi-fungsi pendukung administrasi dan managemen database.

Perintah desain aplikasi saja tidak cukup untuk mendukung  unjuk kerja yang harus didukung dengan optimasi dari perintah SQL. Biasanya dalam mendesain database, seringkali lokasi fisik database tak diperhatikan dengan detail  dan hanya desain logika saja yang diperhatikan.  Padahal untuk menampilkan sebuah hasil  suatu query dibutuhkan pencarian yang melibatkan struktur fisik penyimpanan data. Jadi dapat disimpulkan bahwa tujuan dari optimasi query adalah meminimalisir  jalur dalam   pencarian data yang disimpan  dalam data fisik.

Dalam database index digunakan untuk meningkatkan kecepatan akses data. Pada saat query dijalankan, index bertugas mencari  data-data dan menentukan nilai ROWID yang membantu menemukan lokasi dari data fisik di suatu disk. Apabila penggunaan index yang tidak tepat, maka tidak ada yang mempengaruhi unjuk kerja dalam hal ini adalah kecepatan akses data.

SQL sendiri merupakan sebuah bahasa atau pemrograman stkitar 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 stkitar 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 dilakukan, untuk setidaknya memperbaiki atau mencegah permasalahan, dan meningkatkan performa RDBMS.

Index

Optimasi pertama yang kita bahas adalah permasalahan index. Index dapat meningkatkan kecepatan pencarian pada record yang diinginkan. Tetapi, kita harus cukup selektif dalam memilih field yang perlu di-index, karena tidak semua field memerlukannya. Ibaratnya membaca buku, proses pencarian atau scan akan membaca dari awal hingga akhir halaman. Pada field 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 field tertentu, index scan dapat mengurangi operasi pembacaan I/O sehingga tidak jarang menghasilkan kinerja yang lebih cepat. Sebagai patokan, Kita dapat menentukan index pada field yang sering digunakan, misalnya field yang sering diakses oleh klausa WHERE, JOIN, ORDER BY, GROUP BY.

Pada saat query dijalankan, index mencari data dan menentukan nilai ROWID yang membantu menemukan lokasi data secara  fisik di disk. Akan tetapi penggunaan index yang  tidak tepat, tidak akan meningkatkan kecepatan akses data.

Misal  digunakan  index  yang  melibatkan  tiga  buah  kolom  yang  mengurutkan kolom menurut kota, propinsi dan kode pos dari tabel karyawan, sebagai berikut :

CREATE INDEX idx_kota_prop_kodepos

ON karyawan(kota, propinsi, kode_pos)

TABLESPACE INDX;

Kemudian user melakukan query sebagai berikut :

SELECT * FROM karyawan WHERE propinsi=’Jawa Barat’;

Pada  saat  melakukan  query  ini,  index  tidak  akan  digunakan  karena  kolom  pertama (kota)  tidak  digunakan  dalam  klausa  WHERE.    Jika  user  sering  melakukan  query  ini, maka  kolom  index  harus  diurutkan  menurut  propinsi.    Selain  itu,  proses  pencarian  data akan  lebih  cepat  jika  data  terletak  pada  block  tabel  yang  berdekatan  daripada  harus
mencari di beberapa datafile yang terletak pada block yang berbeda.

Misal pada perintah SQL berikut ini :

SELECT * FROM karyawan

WHERE id BETWEEN 1010 AND 2010;

Query  ini  akan  melakukan  “scan”  terhadap  sedikit  data  block  jika  tabel  karyawan diatas  diurutkan  berdasarkan  kolom  id.    Untuk  mengurutkan  berdasarkan  kolom  yang berbeda-beda  maka  tabel  disimpan  dalam  flat  file,  kemudian  tabel  diekspor  dan diurutkan sesuai kebutuhan.

Alternatif  yang  lain,  bisa  digunakan  perintah  untuk  membuat  tabel  lain  yang memiliki urutan yang berbeda dari tabel asal, seperti perintah SQL berikut :

CREATE TABLE karyawan_urut

AS SELECT * FROM karyawan

ORDER BY id;

Pada  SQL  diatas,  tabel  karyawan_urut  berisi  data  yang  sama  dengan  tabel  karyawan hanya datanya terurut berdasarkan kolom id.