Basis Data dan Sekitarnya..
untuk yang suka pakai PHP dan cURL ,
seringkali saat terjadi trouble kita akan kesusahan untuk mendebug nya,
untuk itu, langkah pertama dari semua proses debugging itu dimulai dari menampilkan seluruh aktivitas dari cURL
hal itu dapat dilakukan dengan script di bawah :
yang perlu diperhatikan untuk ditambahkan pada kode anda adalah bagian didalam if($debug) dan if($result === FALSE && $debug) ..
happy coding
Kamu siapa kamu ? dia siapa dia ?
langsung saja gaiss.. jangan melihat judul itu, langsung saja yang ingin saya bahas adalah :
di environtment sqlserver. tentunya setelah belajar mengenai TSQL , kita harus move on kan ? hehehe.
alasan utama kenapa harus membuat stored procedure dan function adalah modularity yang berujung pada reuse dari stored procedure dan function tersebut.
Dengan stored procedure, kita dapat menyimpan sekumpulan TSQL yang kita buat ke dalam sebuah procedure yang tersimpan rapi di hati sqlserver. Dengan tersimpan, kita dapat memanggil manggilnya sesuka hati kita, kapan saja dia siap membantu kita…
sungguh setia bukan !
mari kita mulai dari yang paling simple saja, seperti :
Pada baris 9 dapat dilihat sintaks untuk membuat simple procedure, dimana terdiri dari nama procedure lalu block procedure dimulai dari begin dan diakhiri sampai end, yang mana dalam kasus yang ada di atas kita akan menghitung jumlah baris dari table person yang ada pada database adventure works 2012. Untuk menjalankan stored procedure ini kita dapat menggunakan perintah exec seperti pada baris 21.
nah yang simple memang sesuai namanya, simple, bagaimana jika jumlah baris yang kita hitung tidak semua person, namun person dengan kriteria tertentu, untuk itu kita dapat menambahkan parameter seperti pada cuplikan berikut :
seperti pada kode baris 9 , kita dapat menambahkan varibael parameter @min_len_middle_name yang bertipe int dengan nilai default 3, sehingga untuk memanggilnya kita dapat menggunakan dengan berbagai cara seperti pada
kedua contoh stored procedure di atas menggunakan tabel sebagai tempat untuk melihat jalannya stored procedure, jika kita tidak menginginkan cara itu, kita dapat membuat stored procedure dengan mempunyai nilai kembalian seperti :
dari kode, saya membuat modifikasi, yaitu sekarang parameter tidak hanya 1 variabel, namun menjadi 3 variabel, dimana 2 variabel pertama adalah varibel input dan varibel terakhir sebagai variabel output. Perbedaaannya adalah terletak pada keyword OUT yang ada di akhir variabel output.
untuk menjalankan stored procedure ini juga cukup mudah yaitu dengan membuat dulu variabel yang akan kita jadikan sebagai variabel output dan menggunakannya pada EXEC seperti pada 18,23,28.
cukup mudah bukan…
Function itu mirip dengan storedprocedure , bedanya dia pasti punya nilai kembalian dan dijalankannya pada level query. Bagaimana maksudnya, maksudnya seperti :
dimana kita mengguakan pre defined function yaitu AVG untuk menghitung rata – rata pada kolom totaldue pada tabel salesorderheader.
function yang sudah pre defined disebut juga sebagai system functions dan untuk function buatan kita (user) disebut sebagai userd defined function atau disingkat sebagai UDF. contohnya adalah function berikut :
di contoh tersebut fungsi yang kita buat adalah mencari nilai minimum dari 2 variabel, dan pada baris 22 , fungsi itu digunakan untuk mencari nilai minimum antara kolom freight dan subtotal pada tiap barisnya.
kegunaan lain dari function adalah untuk menghasilkan table , seperti pada contoh berikut :
di mana terlihat bahwa fungsi akan mengembalikan table yang merupakan hasil query. Perbedaannya adalah di sini fungsi tidak membutuhkan begin end melainkan langsung melakukan return table. Untuk memanggil fungsi jenis ini adalah dengan menganggap bahwa fungsi ini sebagai table, seperti pada baris 19.
so mudah bukan melakukan pembuatan fungsi..
happy coding all..
Di dunia per basis dataan kita mengenal sebuah bahasa pemrograman deklaratif [1] yaitu SQL. Berbeda sekali yang mungkin suka bergulat di dunia pemrograman ria.. yang mana bahasa yang lebih populer yaitu bahasa dengan paradigma pemrograman imperatif [2] seperti Java, c , c++, c# dan lainnya, walaupun ada juga bahasa yang sifatnya deklaratif misalnya saja haskel.
Sqlserver merupakan salah satu produk dari microsoft yang gencar sekali mengadopsi sql ini,selain linQ [3]. Namun dalam perkembangannya microsoft mungkin melihat dengan sql saja (dan sql spesific sqlserver) belum dapat menyelesaikan semua problem yang bisa saja baru dan kompleks, maka mereka menambahkan fitur baru di implementasi sql mereka yang mereka namakan TSQL (Transact-SQL) [4].
Dengan menggunakan TSQL, kita dapat melakukan pemrograman secara imperatif di environtment sqlserver yang tentunya hal ini akan memperkaya kemampuan sqlserver itu sendiri.
mari kita lihat cuplikan kode berikut :
Anda dapat mengetikkan cuplikan kode itu langsung di sqlserver management studio atau apapun yang menjadi sql client anda, anggap saja itu bagian dari query , mudah saja.
jika anda lihat pada baris 1, terdapat sebuah deklarasi variabel yang kita beri nama @vars yang akan bertipe float. Untuk tipe tipe lainnya dapat anda lihat di website microsoft [5].
lalu pada baris 3 akan terlihat operasi untuk memberi nilai pada variabel @vars dengan nilai 1, dan akhirnya kita akan tampilkan dalam baris ke 5.
cukup mudah bukan.. apalagi jika anda adalah seseorang dengan basic pemrograman imperatif yang cukup mumpuni pasti akan mudah mengikuti alur nya.
pada cuplikan sebelumnya pada baris ke 3 kita memberi nilai adhoc yang merupakan contoh operasi pemberikan nilai, nah sekarang bagaimana jika kita menginginkan nilai dari variabel @vars berupa nilai yang kita ambil berasal dari data ?, cukup mudah rupanya mari kita lihat cuplikan berikut :
anda dapat lihat caranya benar benar mudah yaitu dengan yang saya lakukan di baris ke 6. Maka kita dapat lihat hasilnya dengan melakukan print di baris ke 8. Untuk melihat nilai variabel anda dapat menggunakan perintah print atau perintah select tergantung pada apa yang ingin anda hasilkan, jika ingin menghasilkan nilai berupa text saja ( akan tampil di tab message) maka anda cukup menggunakan print, namun jika ingin dijadikan sebuah tabel maka anda bisa menggunakan keyword select.
Untuk melakukan percabangan caranya juga sangat mudah , seperti pada cuplikan berikut :
dimulai dari baris ke 9 untuk mengisi kondisi dari percabangan dan dilanjutkan dengan membuat begin – end pada baris 10 dan 12, hal ini juga sama dengan kondisi ‘else’ nya.
dalam banyak kasus saya memudahkan pengertian saya mengenai begin – end dengan menganalogikannya dengan ‘{‘ ‘}’ pada pemrograman berbasis ‘{‘ seperti java , php .
contoh yang lain dapat kita lihat pada cuplikan berikut :
kali ini kita membuat 2 variabel, yang mana keduanya diambil dari data. pada baris ke 12 dan 16 dapat dilihat statement conditional yang menarik, kenapa menarik, karena mungkin tidak terbiasa bagi yang terbiasa menggunakan java atau php.
Pemrograman tanpa perulangan seperti sayur tanpa garam.. kurang mantap goyangannya..
mari kita lihat cuplikan berikut :
cara nya cukup mudah bukan ? , saya menggunakan metode paling simple dalam perulangan yaitu metode while do.
untuk baris 12 anda dapat melihat sebuah operasi baru yaitu cast. Dengan cast saya dapat mengubah sebuah tipe data variabel ke tipe data tertentu, dan yang saya lakukan adalah merubah tipe data variable tsb (int) ke dalam varchar agar dapat dilakukan proses concatenate dengan operator +. Sebenarnya kalo dilihat lagi mirip dan tidak mirip dengan java, di java kita dapat melakukan System.out.print(“Halo umur saya “+umur); , bedanya adalah di java terjadi automatic conversion antara tipe data tsb, dimana tipe data primiritf akan di ubah menjadi String, sedangkan jika tipe data referensional akan digunakan method toString().
Sepertinya contoh di atas terlalu simple, bagaiamana jika ingin melihat atau melakukan operasi yang langsung melibatkan data dari table ? mari kita lihat contoh yang lebih kompleks :
tujuan dari kode tsb adalah untuk menampilkan totaldue dari 10 transaksi terakhir.tahap pertama dapat anda lihat di baris 8 , saya menggunakan tipe data table dengan kolom yaitu nomor(int) dan id(int), yang bertujuan menyimpan nomor urut dan id dari salesorder. Untuk mengisi variabel ini, operasi insert into select digunakan, seperti pada baris 15, dan untuk mengisi nomor urut digunakan function row_number() yang sudah saya bahas pada postingan membuat query paging di sqlserver sebelumnya. Dan proses utama terdapat di baris 25 – 29.
di Sqlserver kita dapat menggunakan cursor untuk melakukan perulangan seperti kode terakhir yang kita lihat. Contohnya sebagai berikut :
kita dapat mendefinisikan cursor seperti pada baris 7 dan 9, lalu dilanjutkan pada proses penggunaan cursor pada baris 16 yang sebelumnya kita buka pada baris 15. Proses tersebut akan memindahkan nilai query pada cursor dan disimpan pada sebuah variabel yang nanti akan kita tampilkan pada baris 20.
Beberapa catatan untuk penggunaan cursor ini adalah seringkali programmer lupa untuk menambahkan perintah yang sama pada baris 16 ke baris 22 (sebelum end) , hal itu dibutuhkan untuk cursor tetap berjalan.
catatan lainnya adalah jangan lupa untuk menutup dan melakukan deallocate pada variabel cursor tersebut.
mudah bukan TSQL.. , jadi gak perlu dikit dikit php , dikit dikit java …….
happy coding semua…
[1] https://en.wikipedia.org/wiki/Declarative_programming
[2] https://en.wikipedia.org/wiki/Imperative_programming
[3] https://msdn.microsoft.com/en-us/library/bb397926.aspx
setelah melihat bagaimana menggunakan paging di sqlserver, sekarang saya akan mencoba mengulas sedikit mengenai kemampuan sqlserver yang lain yaitu Common Table Expression (CTE) [1]. Dengan CTE ini kita dapat membuat query kita lebih simple.
mari ambil contoh salah satu query dari postingan saya sebelumnya, yaitu query
pada query itu kita akan menambahkan kolom nomor baris, dan selanjutnya akan dilanjutkan dengan query
yang akan melakukan seleksi baris yang akan kita ambil dan tampilkan.
nah sekarang kita coba dengan menggunakan cara yang sedikit berebeda , yaitu menggunakan CTE. Untuk melakukan itu, kita membutuhkan keyword with.
berikut langkah – langkah sederahananya :
tahap 1 dimulai dari kita membuat CTE nya, seperti pada query :
setelah itu kita dapat menambahkan query dibawahnya dengan memanggil CTE tersebut, seperti pada query penuh seperti :
maka kita dapat menggunakan CTE sebagai alternatif dalam menuliskan paging tanpa nested query.
contoh pada web microsoft [1] memberikan contoh yang baik untuk hal ini , misalnya saja kita mempunyai query untuk menghasilkan jumlah penjualan (transaksi) per Sales, maka dapat kita selesaikan dengan query
dan untuk menghitung rata – rata penjualan per sales maka akan kita dapatkan dengan query
yang mana sama dengan query
jika kita tidak menggunakan CTE.
misalkan saja berdasarkan contoh di atas, kita ingin melihat siapa saja yang sales yang diatas rata – rata maka dengan mudah kita mendefinisikan query sebagai berikut :
jauh lebih simple daripada kita menggunakan nested query berikut :
masalah ini sangat terkait dengan masalah yang terkenal di dunia design yaitu masalah rekursif, contohnya adalah design pada masalah employee-manager [2]. Dalam gambar kira2 bisa digambarkan seperti :
untuk menggenerate masalah ini sebelumnya kita perlu membuat tabel untuk itu, karena di adventure works 2012 tidak ada permasalahan rekursif ini secara mudah untuk dilihat. Untuk itu kembali kita lihat contoh dari website microsoft [1]. Yaitu menggunakan query :
dengan ini kita akan membuat table myEmployees yang berisikan beberapa data dummy.
maka untuk menampilkan list employee beserta managernya kita bisa memanfaatkan query
di dalam query itu kita menggunakan (memanggil ) CTE di dalam deklarasi CTE itu sendiri
so asik bukan.. silakan untuk melihat – lihat contoh lain pada [1].
[1]. https://msdn.microsoft.com/en-us/library/ms175972.aspx
[2] http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php
seandainya kita akan membuat halaman web untuk menampilkan data penjualan dari database adventurework 2012 [1] seperti pada query :
maka kit memerlukan solusi paging untuk itu. Karena jika query itu dijalankan maka akan menampilkan data sebanyak 121.317 baris.. tentu hal yang mustahil menampilkan data sebanyak itu dalam 1 halaman web saja.
pada sistem database yang lain misalnya mysql , postgresql atau sqlite maka akan dikenal keyword limit dan offset untuk melakukan paging [2], namun hal itu tidak berlaku di sqlserver.
untuk melakukan paging di sqlserver kita memerlukan beberapa langkah secara umum :
untuk proses nomor 2 , kita dapat memanfaatkan function dari sqlserver yaitu row_number() [3], seperti pada query :
setelah melakukan number maka kita dapat jadikan hal tersebut sebagai nested query dan memfilter datanya berdasarkan kolom number seperti pada query :
khusus pada sqlserver 2012 ke atas, paging dapat dilakukan dengan lebih simple dengan memanfaatkan keyword offset fetch [4], dengan langkah – langkah secara umum sebagai berikut :
seperti pada query :
mudah bukan….
happy coding semua…
Referensi
[2] http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server
[3] https://msdn.microsoft.com/en-us/library/ms186734.aspx
[4] https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx
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 :
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 :
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 :
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/
Indexing merupakan salah satu cara untuk mempercepat kinerja dari sistem basis data. Namun banyak dari kita yang belum memahami dengan baik index tersebut. Maksud saya dari belum memahami, yaitu dengan menggunakan misalnya kondisi default dari index. Misalnya saja, pada pengguna SQLSERVER maka secara default saat anda menggunakan SQLSERVER Management Studio untuk membuat tabel dengan sebuah primary key, maka kolom yang dijadikan primary key tersebut akan dibuat sebuat index clustered. Padahal, kita mempunyai opsi lain yaitu non-clustered, lalu apa bedanya ? apa efek dari penggunaannya ?
dalam situs sqlserver sebenarnya telah dijelaskan dengan umum apa bedanya [1],[2]. Struktur kedua index tersebut sebenarnya mirip , yaitu menggunakan B+Tree Index [5]. hanya saja terdapat perbedaan yang sangat fundamental pada leaf node masing – masing jenis index tersebut.
pada gambar di atas adalah gambar index clustered yang saya ambil dari situs [1]. Pada gambar tersebut terlihat struktur standard index B+Tree yang mana leaf node nya mengarah langsung ke data. Di situs yang lain, Pakde Markus Winand [3] memaparkan dengan lebih detail dan lebih jelas menurut saya. Menurut beliau, dengan menggunakan clustered index, sistem akan memaksa leaf node berurutan sesuai dengan urutan dari index nya, bahasa saya sih leaf node akan berjajar rapi. Sehingga jika saya bayangkan, bahwa dengan menggunakan clustered index ini, menjadikan model penyimpanan data kita menjadi tightly coupled dengan index, artinya tidak ada lagi pemisahan antara index dengan leaf node (atau data). Dengan begitu juga dapat dibayangkan bahwa sebuah tabel pasti tidak bisa memiliki lebih dari 1 clustered index.
berbeda dengan non clustered, dari namanya saja sudah dapat dibayangkan kalau itu merupakan lawan dari clustered :D.
Gambar di atas merupakan gambar struktur index non clustered yang saya ambil dari situs [2]. Dari gambar tersebut terlihat struktur terpisah menjadi 2 yaitu (root node + leaf node) dan data pages. Untuk (root node + leaf node) merupakan struktur non clustered indexnya sendiri dan untuk data pages ini bisa langsung data (heap) atau bahkan clustered index. Artinya bisa saya katakan jika di dalam tabel tersebut tidak ada clusterd index, maka data pages ini akan berupa heap structure yang menyimpan data ( bayangkan saja seperti linked list [4] ). Namun jika dalam tabel tersebut terdapat clustered index, maka data pages nya akan mengacu pada clustered index tersebut. Dari gambar ini dapat kita lihat jika non clustered index lebih bersifat loosly coupled, yang mana keberadaan index tidak akan mempengaruhi bagaimana data tersebut disimpan.
Pakde Markus Winand [3], memiliki gambar yang lebih ciamik lagi untuk menggambarkan keadaan index yang menggunakan clustered atau tidak, seperti yang terlihat pada gambar – gambar di bawah ini.
pada gambar di atas , merupakan contoh struktur non clustered index pada tabel yang tidak memiliki clustered index. Sehingga pada leaf node nya akan memiliki referensi RID ke data pages nya. Hal ini merupakan cost tambahan pada pencarian data karena akan melakukan RID lookup ke data untuk mencari kolom – kolom yang dibutuhkan pada query yang diinginkan.
Sedangkan pada gambar di atas merupakan contoh non clustered index pada sebuah tabel yang memiliki clustered index. Perbedaannya adalah leaf node pada non clustered index tidak mengacu pada RID lagi namun kepada ID primary key dari clustered index. Sehingga tidak terjadi RID lookup namun key lookup. Mengapa keadaan ini terjadi, mengapa tidak tetap menggunakan RID ?. Hal ini karena sifat tightly coupled dari clustered index terhadap data tersebut. Dengan sifat tersebut maka data akan diatur sedemikian rupa untuk mengikuti aturan dari clustered index yang berbasis b+tree. Jika terdapat penambahan data atau pengurangan data, maka sebuah b+ tree pasti akan melakukan penyesuaian – penyesuaian agar tree yang ada tetap balance, karena dengan keadaan balance tree tersebut lebih menjamin kecepatan dalam melakukan pengaksesan data ( tree traversal ). Hal itu berarti juga data yang tersimpan mungkin saja akan mengalami pergerseran tempat disesuaikan dengan keadaan clustered indexnya. Sehingga RID tidak dapat dijadikan patokan tempat bernaung sebuah data, karena data dapat berpindah sesuai dengan keadaan terkini. Hal ini disebut oleh pakde MarkusWinand sebagai Living Creature. Dan hal tersebut berarti biaya dari non clustered index akan ditambah biaya pencarian clustered index. Hal itu disebut sebagai clustered index penalty oleh pakde MarkusWinand.
Setelah membaca beberapa paparan di atas, mungkin anda melihat tendensi saya sedikit memojokkan clustered index, sebenarnya tidak demikian namun memang dikarenakan saya melihat clustered index memiliki kemampuan yang baik namun pada kasus kasus tertentu.
Secara umum , guideline dan kelebihan clustered index dan non clustered index dapat dilihat pada [6], [7]. Namun guildeline itu bagi saya masih sangat ambigu dan tidak menjawab dengan tegas dimana letak kelebihan masing – masing model index. Untuk itu pada post kali ini kita akan menggunakan percobaan kita sendiri. Kita akan membuat 2 buah tabel yang identik secara kolom namun berbeda secara penggunaan clustered index.
untuk tahap awal mari kita membuat tabel seperti berikut :
pada Script di atas, kita akan membuat 2 tabel yang memiliki kolom :
kedua tabel memiliki definisi index constraint PK yang berbeda , salah satu menggunakan constraint CLUSTERED yang merupakan settingan default dari sqlserver, sisanya menggunakan constraint PK yang NON CLUSTERED.
selain itu kedua tabel ditambahkan index NON CLUSTERED pada kolom umur.
Setelah itu, data [8] yang berjumlah 700.000 record dimasukkan ke dalam kedua tabel.
untuk percobaan ini kita melakukan 2 query .
hasil dari query tersebut adalah :
sedangkan untuk query :
hasilnya adalah :
dari hasil di atas beberapa variabel yang dapat kita amati adalah :
Jika kita melihat hasilnya, dapat dilihat bahwa untuk index non clustered akan menghasilkan logical reads yang lebih banyak dibandingkan dengan index clustered. hal ini dikarenakan adanya biaya RID lookup pada masing – masing item pada leaf node di index non clustered (silakan lihat lagi gambar index non clustered) . Berbeda dengan index clustered yang datanya telah terintegrasi dengan struktur B+ Tree, maka tidak perlu banyak logical reads untuk mendapatkan data tersebut. Index clustered hanya membutuhkan titik leafnode awal untuk start dan berakhir pada 10000 (select top 10000) data dengan cara direct linked list traversal tidak lagi menggunakan tree traversal. Dari sini dapat kita lihat kelebihan dari index clustered.
untuk Query
hasilnya :
sedangkan untuk Query :
hasilnya :
untuk hasil dengan keyword between, kembali primary keys (PK) dengan index clustered sangat unggul dari PK dengan index non clustered. Hal ini jelas memang keunggulan dari index clustered yang tidak memiliki RID lookup sehingga hanya memerlukan 1 kali tree traversal untuk menemukan data dengan nilai terendah dan dilanjutkan dengan linked list walking pada data tersebut sampai menemukan nilai tertinggi, maka dengan itu memang logical reads nya jadi sangat rendah yaitu 3 , yang mana asumsinya adalah hal ini merupakan tree traversal. Berbeda dengan index non clustered yang mana memiliki logical reads sampai 57. Hitungan – hitungan kasar merupakan ( jml hop pada tree traversal + jml RID lookup) => (3 (asumsi tree travelsal sama dengan clustered index) + 54 (jml data)). Hal ini berarti memang untuk tiap item data akan dilakukan RID lookup pada data, hal ini yang menyebabkan logical reads menjadi tinggi.
pada percobaan ini kita ingin melihat apa ada efek penggunaan index clustered pada index non clustered dalam 1 buah tabel .
untuk query
hasilnya :
sedangkan untuk query
hasilnya :
dari hasil tersebut dapat dilihat bahwa clustered index sangat mempengaruhi non clustered index dalam 1 tabel. Pada tabel yang memiliki clustered index, nilai logical reads bisa sampai 3 kali lipat dari tabel yang tidak memiliki clustered index. Hal ini yang bisa disebut sebagai clustered index penalty. Karena leaf node index non clustered tidak langsung mengarah pada physical address dari data (RID) namun leaf node akan berhubungan dengan key dari index clustered. Dengan kata lain kita akan punya 2 kali tree traversal untuk mendapatkan sebuah record.
Dari percobaan di atas, dapat dilihat penggunaan clustered index pada tabel sangat powerfull untuk range query pada kolom yang dijadikan index tersebut. Hal ini dikarenakan sifat dari clustered index yang terintegrasi dengan data sehingga data menjadi terurut dan teratur sesuai aturan index tersebut. Di lain sisi , non clustered index menggunakan RID lookup yang menjadi titik lemah saat query range ini.
Namun dengan adanya clustered index pada sebuah tabel, membuat index lain (pada kolom lain) yang pastinya non clustered terpengaruh. Hasil ujicoba memperlihatkan bahwa tabel dengan PK non clustered akan mengungguli tabel dengan PK clustered. Hal ini dikarenakan adanya clustered index penalty yang membuat table dengan PK clustered harus melakukan 2 tree traversal.
Sistem default dari SQLSERVER yang langsung menganggap PK adalah clustered perlu diwaspadai oleh pengguna, karena sangat mempengaruhi kinerja dari sistem selanjutnya. pertanyaan yang mungkin dapat membantu menentukan menurut saya adalah :
jika bobot nya lebih banyak pada no 1, yaitu lebih ke arah PK maka memang lebih baik menggunakan clustered index, namun saya yakin bahwa akan lebih banyak kasus lebih menitik beratkan pada no 2, yang mana non clustered PK lebih prefer untuk digunakan. Mengapa demikian ? karena menurut pengetahuan saya, kebanyakan PK digunakan oleh sistem modern sekarang adalah sebagai surrogate key [9], yang mana sangat jarang untuk kita melakukan query range tersebut.
bagaimana dengan anda ?
[1] https://msdn.microsoft.com/en-us/library/ms177443.aspx
[2] https://msdn.microsoft.com/en-us/library/ms177484.aspx
[3] http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key
[4] https://en.wikipedia.org/wiki/Linked_list
[5] https://en.wikipedia.org/wiki/B%2B_tree
[6] https://msdn.microsoft.com/en-us/library/ms190639.aspx
[7] https://msdn.microsoft.com/en-us/library/ms179325.aspx
[8] https://github.com/radityopw/blog_files/blob/master/clustered%20vs%20non-clustered%20index%20sqlserver/data_person.csv
[9] https://en.wikipedia.org/wiki/Surrogate_key
Octave.. mainan lainnya sebagai alternatif MATLAB bagi orang yang gak punya duit untuk beli MATLAB, atau yang gak suka ngecrack MATLAB 😀
matlab ??!
yoa , ceritanya saya mau siap – siap untuk kuliah lagi, masalahnya kuliah lagi ini mau gak mau harus lebih “scientific” sedikit lah… alkisah akhirnya belajar octave ini.
Namanya juga peneliti dadakan yang sehari – hari lebih banyak mroyek ngoding ketimbang mbuat paper, ya hal pertama yang diliat gak jauh jauh dari dunia perkodingan. hahahaha payah..!
yang diliat bukannya bagaimana ngitung FFT atau nyelesaikan intergral lipat 10 pake octave, tapi malah bagaimana menggunakan class Java yang kita buat di dalam octave.
[percobaan 1]
x = javaObject (“java.lang.StringBuffer”)
y = javaMethod(“append”,x,”hello”)
y = x.append(“hello”);
z = javaMethod(“toString”,y)
z = y.toString();
wah cukup mudah ya, ternyata santai jaya…
bagaimana kalau class yang mau digunakan adalah buatan kita sendiri ?
[percobaan 2]
javaaddpath(“<LOKASI folder yang ada dicoba/Dicoba.class atau .jar>”)
x = javaObject(“dicoba.Dicoba”)
y = javaMethod(“masukin”,x)
z = javaMethod(“masukin”,x,”world”)
wah simple dan nyantai banget.. dengan ini sebenarnya kita yang bisa Java dapat mengembangkan fitur yang tidak dimiliki oleh octave.. 😀
happy coding …
[1] https://gist.github.com/radityopw/17d0fd3ad701d91f6c31
untuk kebutuhan tertentu, mungkin anda membutuhkan netbeans yang bukan versi baru.
kali ini saya mencoba menginstall netbeans 6.8 di windows 8,
ternyata cukup menjengkelkan , dengan JDK 8 yang saya telah install muncul peringatan
setelah mengikuti saran error tersebut saya menginstall jdk 6
namun masih gagal
lalu menginstall jdk 5 gagal juga
akhirnya menemukan link https://forums.netbeans.org/post-140036.html
di sana juga ternyata cukup lama untuk ada jawabannya,
dan jawabannya cukup simple , yaitu gunakan compability run (yang saya pakai compability ke windows 7)
dan akhirnya netbeans terinstall dengan baik
happy coding