Table Scan Method vs. Clustered Index Method
Pembahasan mengenai metode mana yang memberikan performance lebih baik saat membuat tabel ini sempat dibahas sedikit di milis SQL Server User Group Indonesia . Hal ini menurut saya memang menarik untuk dibahas lebih lanjut dan didiskusikan. Sebelum saya membahas lebih lanjut tentang table-scan method dan clustered index scan method, saya akan membahas sedikit mengenai organisasi data pada SQL Server 2005.
Organisasi data pada SQL Server 2005
Setiap tabel pada database memiilki sebuah tabel sysindex yang memiliki index id (indid) yang berbeda-beda. Indid ini nilainya bervariasi mulai dari 0 hingga 254. Heap table memiliki indid 0, sedangkan clustered index table memiliki indid bernilai 1, dan non-clustered index table memiliki indid bernilai 2-254. Saat dilakukan pencarian data, maka SQL Server akan melihat FirstIAM (Index Allocation Map) tabel tersebut dan mengenali apakah tabel tersebut memiliki index (baik clustered maupun non-clustered) lewat indid pada tabel sysindex tersebut.
Cara kerja table-scan dan clustered index scan
Berikutnya adalah bagaimana cara kerja table scan dan clustered index scan. Ilustrasi mengenai cara kerja table scan dan clustered index scan, pernah saya bahas pada artikel sebelumnya. Table scan method dapat diumpamakan seperti mencari sebuah kata pada tabel yang tidak memiliki index. Anda mungkin dapat menghabiskan waktu untuk mencari kata tersebut pada buku yang saya maksud, sedangkan clustered index scan dapat diumpamakan seperti index huruf pada kamus, Anda hanya perlu mencari huruf yang sesuai dengan huruf pada awal kata yang Anda cari. Hal ini tentu menyebabkan waktu pencarian dengan kedua metode tersebut berbeda. Clustered index scan akan mengoptimalkan waktu pencarian Anda. Demikian pula yang terjadi pada SQL Server. Clustered index table menggunakan struktur B-Tree (root-intermediate-leaf) yang akan membantu optimalisasi waktu pencarian data. Sebenarnya heap table juga menggunakan konsep ini, namun ada perbedaan yang terdapat antara kedua metode ini. Hal tersebut adalah :
- Heap table minimal membutuhkan 4 jump untuk mencari data. Jump tersebut antara lain : pencarian di root page, intermediate page, leaf node, dan data page itu sendiri. Hal ini dikarenakan data pada leaf node adalah pointer data, sehingga SQL Server perlu mencari lagi data yang Anda maksud pada harddisk. Oleh karena itu, IAM pada table scan lebih tepat disebut sebagai Storage Allocation Map (SAM). Hal yang pertama kali dilihat oleh SQL Server saat melakukan table scan adalah FirstIAM (Index Allocation Map) tabel tersebut. Saat indid bernilai 0, yang berarti tabel tersebut merupakan heap table, maka SQL Server akan mencari pada header page database. Hal inilah yang menyebabkan waktu eksekusi pada heap table menjadi lebih lama.
- Clustered index table hanya membutuhkan 3 kali jump untuk mencari data. Jump tersebut antara lain : pencarian di root page, intermediate page, dan leaf node. Saat mencapai leaf node, data yang Anda cari sudah terdapat disana, sehingga waktu pencarian pun akan menjadi lebih singkat. Selain itu, data yang dimasukkan pada tabel yang memiliki clustered index akan 'dipaksa' untuk disimpan secara terurut berdasarkan index. Hal ini akan membuat pencarian data lebih cepat.
Ada beberapa hal yang perlu Anda ingat disini, walaupun pada clustered index table data sudah 'terurut' dengan baik, Anda perlu mengaktifkan update statistic atau secara rutin mengupdate statistic index. Hal ini bertujuan untuk mencegah terjadinya fragmentasi index.
Nah, setelah penjelasan mengenai hal ini, semoga pengertian konsep pencarian data dengan kedua metode ini akan lebih baik dari sebelumnya.Sebenarnya saya agak takut membahas hal ini, dikarenakan takutnya mispersepsi yang mungkin akan timbul setelahnya. Untuk itu saya akan menyertakan referensi yang saya gunakan untuk penulisan artikel ini, agar rekan-rekan bisa mereview ulang artikel saya ini. Semoga berguna buat rekan-rekan.
Referensi yang digunakan :
SQL Server Books Online
MSDN Article : SQL Server Optimization
SQL Druid Article mengenai Optimization in SQL Server
Sybex - Implementation and Maintenance MS SQL Server 2005
Regards,
Rangga Praduwiratna, MCP, MCTS : SQL Server 2005