Optimasi Query

Beberapa hari lalu saya diminta untuk membuat ulang program penghitung harga rata-rata pada tiap akhir hari perdagangan di back-office. Program sebelumnya selalu menghitung pembelian terlebih dahulu baru mengurangi balance saat penjualan, tentu saja hasilnya akan berbeda jika perhitungan dilakukan menurut waktu terjadinya transaksi.

Yang pertama kali saya lakukan adalah membuat Stored Procedure yang kerjanya mengambil data transaksi, lalu mengupdate/insert (data disimpan per tanggal) tabel saldo yg berisi Saldo, AvgPrice, dll, terurut menurut waktu terjadinya transaksi. Stored procedure yang saya buat berjalan lambat, karena pada tiap transaksi stored procedure akan menulis ke table yang berarti akan ada aktivitas harddisk untuk menulis, dan dalam 1 hari bisa ada ribuan (antara 2 sampai 4 ribu) frekuensi transaksi.

Stored procedure saya ubah dengan menambahkan variabel dengan tipe “table”, saya melakukan semua insert ke variabel tabel, lalu di akhir proses saya lakukan bulk insert ke tabel aslinya. Hasilnya stored procedure berjalan 10x lebih cepat.

Table temporary di memory memang cepat, tapi ada kasus lain yang ternyata kebalikannya. Saya punya 1 table yang akan saya bulk update berdasarkan sebuah multi-statement table-valued function (fungsi yang outputnya berupa table). Begitu saya coba jalankan, query update itu berjalan lebih dari 13 menit. Googling sebentar dapet informasi dari MSDN :

Joining to a multistatement table valued function in a FROM clause is possible, but can give poor performance. SQL Server is unable to use all the optimized techniques against some statements that can be included in a multistatement function, resulting in a suboptimal query plan. To obtain the best possible performance, whenever possible use joins between base tables instead of functions.

Saya ubah lagi querynya dengan menyimpan terlebih dahulu hasil fungsi ke tabel fisik dengan menggunakan select into, lalu melakukan bulk-update dan terakhir menghapus kembali tabel penyimpanan sementara itu. Hasilnya query berjalan 39 detik untuk mengupdate 54.827 row data.

Selalu cari jalan tercepat, jangan puas hanya dengan hasil yang benar.

333total visits,1visits today

Leave a Reply

sedjat1