OPTIMASI QUERY MATERI 4 Sistem Manajemen Basis Data

OPTIMASI QUERY MATERI 4 Sistem Manajemen Basis Data Eko Prasetyo Teknik Informatika Universitas Bhayangkara Surabaya 2012

Tabel-tabel yang digunakan 2 lecturer students enrolled faculty course grade_scale

Optimasi Query 3 Data yang tersimpan dalam database semakin lama akan semakin besar ukuran atau volumenya. � Kalau tidak didukung dengan kecepatan akses yang memadai maka akan semakin menurun unjuk kerjanya. Ukuran unjuk kerja dalam hal ini kecepatan akses data dipengaruhi oleh banyak faktor, seperti: perintah SQL, index, denormalisasi, cluster.

Optimasi Pada Perintah SQL 4 Desain aplikasi saja tidak cukup untuk meningkatkan unjuk kerja harus didukung dengan optimasi dari perintah SQL yang digunakan pada aplikasi tersebut. Dalam mendesain database, seringkali lokasi fisik data tidak menjadi perhatian penting. � � Karena hanya desain logik saja yang diperhatikan. Padahal untuk menampilkan hasil query dibutuhkan pencarian yang melibatkan struktur fisik penyimpanan data. Inti dari optimasi query adalah meminimalkan “jalur” pencarian untuk menemukan data yang disimpan dalam lokasi fisik. Index pada database digunakan untuk meningkatkan kecepatan akses data. � � � 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 unjuk kerja dalam hal ini kecepatan akses data. Indek bertindak seperti ‘daftar isi’ dalam sebuah buku.

Optimasi Pada Perintah SQL 5 Operasi-operasi query: seleksi, proyeksi, join Seleksi: Operasi pemilahan baris-baris data yang memenuhi kriteria dalam klausa WHERE � Proyeksi: Operasi pemilihan kolom tertentu dari tabel yang akan ditampilkan � � � Jika tidak terdapat indek pada kolom yang digunakan sebagai operand seleksi, maka DBMS akan men-scan seluruh isi tabel tersebut Select sid, name from students Memilih kolom sid dan name dari tabel students Operasi yang mahal adalah jika menggunakan operator DISTINCT Select DISTINCT age from students Akan mendapatkan usia berapa saja pada mahasiswa dalam tabel students Join: Operasi penggabungan 2 atau lebih tabel, ini merupakan operasi yang umum dan mahal. � Ada 3 jenis: left join, right join, dan inner join (kadang juga menggunakan operator WHERE untuk menggabungkan)

Index 6 Misal: Pada tabel students akan dibuatkan index yang melibatkan 1 kolom yang mengurutkan data menurut nama. � Kemudian melakukan query sebagai berikut: � � � Select a. sid, a. name from students as a where a. name like ‘R%’ Select a. sid, a. name from students as a where a. name in (‘Sandi’, ’Wati’) Select a. sid, a. name from students as a where a. name not in (‘Sandi’, ’Wati’) and a. age > 20 Kedua query akan menggunakan indek secara optimal. � � create index idx_students_name on students (name) Query 1 dan 2: optimal Query 3: optimal untuk name, tidak untuk age (karena tidak ada indek untuk age) Untuk menghapus index, gunakan perintah alter dan drop � Misal: alter table students drop index idx_students_name

Index 7 Misal: Pada tabel students akan dibuatkan index yang melibatkan 2 kolom yang mengurutkan data menurut fid (kode fakultas) dan lid (kode dosen wali) � create index idx_students_fid_lid on students (fid, lid) Kemudian melakukan query sebagai berikut: SELECT a. name, a. birthday from students as a where a. lid in (‘ 9001’, ’ 9003’) Indek tidak digunakan, karena seleksi tidak melibatkan kolom pertama indek � SELECT a. name, a. birthday from students as a where a. fid = ‘ 1’ Indek tidak akan digunakan secara optimal karena pembuangan resource pelibatan kolom lid dalam indek � SELECT a. name, a. birthday from students as a where a. fid = ‘ 1’ and a. lid in (‘ 9001’, ’ 9003’) Indek digunakan secara baik dan optimal. �

Optimasi Aplikasi 8 Dalam pembuatan aplikasi, yang perlu mendapat perhatian adalah apakah akses terhadap data sudah efisien. � Kemudian juga bagaimana cara database didesain. Apakah desain database sudah melakukan normalisasi data secara tepat. � � Efisien dalam hal penggunaan obyek yang mendukung kecepatan akses, seperti index atau cluster. Kadangkala normalisasi sampai level yang kesekian, tidak menjamin suatu desain yang efisien. Untuk membuat desain yang lebih tepat, kadang setelah melakukan normalisasi perlu dilakukan denormalisasi. Misalnya tabel yang hubungannya one-to-one dan sering diakses bersama lebih baik disatukan dalam satu tabel.

Denormalisasi 9 Misal, pada tabel students sering ada pengaksesan antara tabel students dan lecturer untuk menampikan nama dosen wali pada KRS/KHS mahasiswa, untuk kasus seperti ini sebaiknya dilakukan denormalisasi dengan menambahkan kolom ‘lecturer_name’ pada tabel student � Manfaatkan korelasi sub-query update Lakukan denormalisasi tabel students dengan menambah kolom lecturer_name alter table students add column lecturer_name varchar(30) Isi kolom lecturer_name didapatkan dari tabel lecturer update students as a set lecturer_name = (select b. name from lecturer as b where b. lid=a. lid)

Cluster 10 Cluster adalah suatu segment yang menyimpan data dari tabel yang berbeda dalam suatu struktur fisik disk yang berdekatan. � Penggunaan cluster secara tepat dilaksanakan setelah menganalisa tabel-tabel mana saja yang sering di-query secara bersamaan menggunaan perintah SQL join. � � Konfigurasi ini bermanfaat untuk akses data dari beberapa tabel yang sering di-query. Jika aplikasi sering melakukan query dengan menggunakan suatu kolom yang berada pada klausa WHERE, maka harus digunakan index yang melibatkan kolom tersebut. Penggunaan index yang tepat bergantung pada jenis nilai yang terdapat dalam kolom yang akan diindex. Dalam RDBMS Oracle, index B-Tree digunakan untuk kolom yang mengandung nilai yang cukup bervariasi, sedangkan untuk nilai yang tidak memiliki variasi cukup banyak, lebih baik menggunakan index bitmap.

11 Any Question ? To Be Continued … Materi 5