Posisi Menentukan Prestasi
kalimat yang sering saya dengar saat mahasiswa dulu, dimana sering disebutkan saat mau ujian… banyak yang bilang posisi duduk saat ujian menentukan hasil ujian. Hahaha tentu saja hal itu tidak benar dan sangat tidak berdasar… ya kata gaulnya itu cuman “mitos”. Namun berbeda dengan indexing, hal ini bisa jadi benar lho… “mitos” yang menjadi nyata.
Setelah membaca post saya sebelumnya tentang clustered index vs non clustered index [1], secara umum anda akan mengetahui bagaimana struktur index dan bagaimana index itu bekerja.
Langsung saja ke contoh nya ya, misalnya saja kita punya tabel dengan definisi sebagai berikut :
Dengan query di atas kita akan mendapatkan table bernama “[pt1] [pt2] [pt3] ([ptx])” yang memilki kolom :
- nim : int : adalah nomor induk mahasiswa yang menjadi primary key (PK)
- nama : varchar(100) : adalah nama mahasiswa.
selain itu juga ada tabel yang bernama “[all_pt]” yang merupakan gabungan dari ke tiga tabel [ptx] tersebut.
setelah itu kita lakukan pengisisan data dengan data dari [2] ke dalam tabel [pt1][pt2][pt3]. dan kita running query penggabungan pada table [all_pt] sebagai berikut :
setelah hal itu dilakukan, maka kita akan memiliki seluruh data mahasiswa dari pt1, pt2, pt3 di dalam sebuah tabel yang bernama all_pt. hal itu mengakibatkan kolom nim tidak menjadi hal yang unik lagi melainkan kolom nim dan kolom pt.
mari kita lakukan percobaan,
sebelum menjalankan query tersebut kita terlebih dahulu harus mengaktifkan actual execution plan pada sqlserver management studio [3] [4], seperti pada gambar di bawah ini :
(sumber : https://www.mssqltips.com/sqlservertutorial/2252/estimated-vs-actual-query-plan/ )
setelah kita mengaktifkan actual execution plan, maka kita akan dapatkan hasil sebagai berikut :
dari grafik di atas, dapat kita lihat database melakukan operasi table scan yang dilanjutkan dengan operasi parallelism. Yang menjadi sorot utama kita kali ini adanya operasi table scan yang berarti database melakukan pembacaan pada keseluruhan tabel tanpa menggunakan index sama sekali. Hal tersebut tentu sangat tidak kita inginkan.
Namun hal tersebut juga tidak aneh, karena saat mendefinisikan tabel all_pt, kita sama sekali tidak mendifinisikan index yang ada. Untuk itu mari kita definisikan index berdasarkan query di atas.
Pembuatan index di atas sudah sesuai dengan query yang melakukan query dengan melihat kolom pt terlebih dahulu lalu dilanjutkan dengan kolom nim. Setelah index dibuat mari kita coba query select
Maka akan didapatkan hasil sebagai berikut :
dari gambar dapat dilihat database telah menggunakan index_1 sebagai objek operasi index seek dan mengkombinasikan dengan RID Lookup. Hal ini tentunya sesuai ekspektasi kita dalam penggunaan index yang harapannya dapat memperbaiki kinerja dari query kita.
Untuk melakukan query berdasarkan Pt dan Nim tentu sudah dapat kita selesaikan, namun bagaimana dengan query berdasarkan Nim saja. Misalnya saja kita ingin melakukan list mahasiswa yang memiliki Nim = 40 seperti query :
maka akan kita dapatkan hasil sebagai berikut :
database tidak menggunakan index_1 secara maksimal, walaupun tetap menggunakan index tersebut. hal ini Dapat dilihat dari text hijau yang menandakan adanya missing index.
untuk hal ini kita dapat saja membuat index baru khusus berisi kolom Nim saja (seperti yang disarankan pada missing index), namun perlu untuk diingat , bahwa membuat index berarti menambah kompleksitas dalam hal proses insert, delete , update dari tabel. untuk itu jika bisa kita dapat menggunakan index yang telah ada sebaik mungkin.
database tidak menggunakan index_1 dengan maksimal karena dalam index_1 kolom pertama yang terindex adalah kolom Pt lalu diikuti kolom Nim. Yang dapat kita lakukan adalah dengan memperbaiki index_1 dengan mengatur ulang posisi dari kolom kolomnya, kolom Nim akan kita dahulukan lalu diikuti dengan kolom Pt, seperti pada query :
maka akan kita dapatkan hasil untuk query :
dapat dilihat bahwa penggunaan index_1 lebih maksimal sekarang, selain indikator missing index yang tidak ada, juga jumlah proses dalam database yang lebih sedikit.
jika kembali kita coba dengan query :
maka akan kita dapatkan hasil :
sehingga hasilnya tidak berbeda dengan menggunakan index_1 di awal. ini berarti memang membuktikan bahwa
Posisi Menentukan Prestasi
sebelum saya akhiri catatan saya, saya ada sedikit renungan bagi pembaca yang budiman semua…
kita tahu kita punya 2 kondisi index_1, yaitu :
- dengan urutan pt,nim
- dengan urutan nim, pt
dan dari catatan ini dapat dilihat bahwa untuk query
dan
yang terbaik adalah opsi 2.
nah seandainya kita tetap pada opsi 1 dan melakukan query
mengapa kita mendapatkan hasil seperti di bawah ini ?
Referensi
[1]. http://radityo.pw/2016/01/24/clustered-index-vs-non-clustered-index-pada-sqlserver/
[2]. https://github.com/radityopw/blog_files/blob/master/pmp/datagen.txt
[3]. https://msdn.microsoft.com/en-us/library/ms189562.aspx
[4]. https://www.mssqltips.com/sqlservertutorial/2252/estimated-vs-actual-query-plan/