Bagaimana mengubah stored procedure yang berjalan 16 menit menjadi 1 detik?
Bagaimana mengubah stored procedure yang berjalan 16 menit menjadi 1 detik?
By : Kasim Wirama, MCDBA, MVP SQL Server
SQL Server 2005 memiliki banyak fitur TSQL yang memberikan lebih banyak pilihan dibandingkan dengan versi sebelumnya, karena SQL Server 2005 mengimplementasikan beberapa standard yang ditetapkan dalam ANSI SQL 2003 setelah standard ANSI SQL 99.
Kasus query tuning yang menarik di sini berkenaan dengan implementasi SQL Server 2005 ke dalam stored procedure yang menyebabkan stored procedure tersebut berubah execution time-nya dari 16 menit menjadi 1 detik dengan row kembalian sebanyak 1 row.
Sebagai gambarannya, stored procedure tersebut memiliki beberapa query, dimana sebetulnya yang paling berat execution cost di sini terjadi pada sebuah query yang melakukan join antara 2 tabel, dimana tabel pertama memiliki row sebanyak 600 ribu sedangkan tabel kedua memiliki row sebanyak 53 juta. Join antara kedua tabel tersebut bersifat equijoin, ditambah dengan kondisi yang mengandung function SQL Server seperti replace/coalesce di sisi kolom seperti WHERE coalesce(kolomA,’’)=’’ dikombinasikan dengan filtering dengan input parameter. Sehingga sebetulnya query tersebut sederhana menjadi sangat kompleks.
Semula saya mengira ini hanya persoalan index, setelah dicheck ternyata kolom untuk melakukan joining antara kedua tabel tersebut telah dalam bentuk clustered index. Ada 3 kemungkinan join diluar kolom-kolom yang tidak di index, yaitu
- join antara clustered index dengan clustered index
- join antara non clustered index dengan clustered index
- join antara non clustered index dengan non clustered index
Diantara ketiga join tersebut yang ideal adalah kemungkinan pertama. Berarti seharusnya hasil kembalian 1 row tidak memakan waktu yang lama. Setidaknya itu menurut pengalaman saya melakukan query tuning di beberapa kasus project SQL Server. Saya menduga kerumitan kondisi WHERE yang tidak memenuhi kondisi SEARCH ARGUMENT yang merupakan bottleneck query tidak tidak scalable. Setelah saya normalisasi kondisi WHERE ke dalam bentuk SEARCH ARGUMENT, tetap ada perbaikan kecepatan eksekusi (meski sedikit) dari 16 menit ke 14 menit. L
Berarti normalisasi dan clustered index pada kolom untuk kedua tabel tersebut tidak banyak berpengaruh. Apa yang sebetulnya merupakan cause root lamanya query tersebut? Itu yang menarik untuk di telusuri lebih lanjut. Saya kemudian eksekusi ulang untuk melihat query plan, eksekusi dengan menampilkan query plan biasanya memakan waktu yang lebih lama, dalam kasus ini eksekusi berjalan selama 18 menit. Bila anda sering melihat query plan, tentu anda akan familiar sekali dengan operator di dalam execution plan seperti : nested loop, hash join, merge join, parallelism, dan lain-lain. Untuk kasus ini terdapat nested loop dan juga parallelism. Yang perlu saya kemukakan di sini, bila anda mendapatkan query anda berada dalam lingkup parallelism, berarti ada 2 kemungkinan, kemungkinan pertama adalah anda melakukan searching ke dalam setiap row di dalam sebuah tabel (performance problem akan terjadi bila jumlah row dalam tabel tersebut sangat besar) sehingga query optimizer memutuskan untuk membagi peran row retrieving ke sejumlah prosesor à berarti ada pemborosan resource server, kemungkinan kedua adalah query tersebut yang tidak optimal, sehingga akses data ke tabel-tabel yang terkait melebihi dari apa yang seharusnya dibaca, sebagai contoh untuk mendapatkan 300 row, query optimizer melakukan pembacaan lebih dari 300 row à berarti juga pemborosan resource server. Kedua solusi permanent untuk kedua kemungkinan tersebut adalah refactor query atau yang anda perlu tahu adalah query tuning. Operator lainnya yang perlu anda perhatikan adalah nested loop. Nested loop merupakan indicator yang tidak cukup baik karena apabila anda melihat jumlah row yang besar di dalam nested loop, berarti terjadi looping row dalam jumlah besar sehingga yang perlu saya lakukan di sini adalah memeriksa apakah penerapan index yang terkait sudah benar (mungkin juga belum tercreate index) atau anda membatasi jumlah looping di dalam nested loop. Trick dalam query tuning yang perlu anda ketahui. J
Saya melakukan perubahan dari inner join menjadi cross apply untuk meminimalisir akses row yang berlebihan, dan memindahkan criteria yang kompleks yang telah dinormalisasi (SEARCH ARGUMENT) dari dalam criteria WHERE ke dalam Common Table Expression (CTE).
Setelah saya coba banding execution cost antara query lama dengan query yang saya buat, perbandingannya adalah 99%:1%. Saya amati ternyata operator parallelism telah hilang dan jumlah query hasil implementasi cross apply menjadi berkurang dari 41000 row menjadi hanya 1 row. Saya coba jalankan query baru tersebut, waktu eksekusi memakan waktu 1 detik.
Tampaknya fitur T-SQL 2005 memberikan nilai tambah bagi performance database query.