Meningkatkan Performa Stored Procedure

Kita lebih sering menggunakan stored procedure dibandingkan query yang panjang pada arsitektur client-server. Client hanya akan mengirimkan nama stored procedure(dan parameter) ke server. Hal ini akan memperingan kinerja dari transfer antara client dan server serta menampik isu-isu keamanan(menyembunyikan object data).

Dengan seringnya pemakaian strored procedure, maka kita juga perlu memperhatikan pembuatan stored procedure agar semakin efektif. Berikut ada beberapa tips dalam pembuatan stored procedure:

1. Pergunakanlah statement SET NO COUNT ON
Ketika kita melakukan query SELECT atau DML pada sql maka SQL akan mengembalikan jumlah rows affected. Dengan SET NO COUNT ON, jumlah rows affected tidak akan muncul lagi. Hal ini akan mengurangi traffic pada network.

2. Pergunakanlah nama schema dan objectnya
Pada stored procedure mempergunakan nama schema sebelum kita melakukan query suatu object akan mempermudah SQL Server dalam mencari object yang ingin dituju. Dengan ini SQL Server akan mencari object lebih cepat.
contoh:
SELECT * FROM   dbo.mst_books 


3. Jangan mempergunakan awalan "SP_" pada nama stored procedure
Awalan "SP_" digunakan oleh nama stored procedure pada system. SQL Server akan mencari stored procedure dengan awalan "SP_" pada database master terlebih dahulu. Pencarian ini akan memakan waktu dan jika stored procedure yang dibuat oleh user sama dengan stored procedure pada database master, maka stored procedure yang dibuat oleh user tidak akan pernah dieksekusi.


4. Pergunakanlah stored procedure sp_executesql daripada statement EXECUTE
Menggunakan sp_executesql membuat dynamic query kita dapat digunakan kembali. Ketika kita mengubah parameter pada sp_executesql, SQL Server akan menggunakan execution plan yang sama dengan sebelumnya. Sebagai contoh :
DECLARE @Query VARCHAR(100) 
DECLARE @Age INT 

SET @Age = 19 
SET @Query = 'SELECT * FROM dbo.mst_mahasiswa WHERE Age = ' + 
             CONVERT(VARCHAR(3), @Age) 

EXEC (@Query)


Pada contoh diatas, query diatas tidak reusable. Ketika kita mengganti age dengan 20, SQL Server tidak akan menggunakan execution plan yang sama dengan query sebelumnya.


DECLARE @Query NVARCHAR(100)

SET @Query = N'SELECT * FROM dbo.mst_mahasiswa WHERE Age = @Age'

EXECUTE Sp_executesql @Query, N'@Age int', @Age = 19  

EXECUTE Sp_executesql @Query, N'@Age int', @Age = 20

Dengan sp_executesql, SQL Server akan menggunakan execution plan yang sama, dimana itu akan mempercepat proses kerja SQL.


5. Pergunakanlah IF EXISTS(SELECT 1) daripada (SELECT *)
Untuk mengecek apakah ada suatu record dalam suatu table, lebih baik kita menggunakan IF EXISTS(SELECT 1) daripada IF EXISTS(SELECT *). Hasil dari internal query SELECT * akan berarti sama saja dengan SELECT 1, karena dua-duanya akan berarti true. Untuk meminimalis proses maka kita tidak memerlukan record set yang komplit. Contoh :


IF EXISTS (SELECT 1 
           FROM   sysobjects 
           WHERE  name = 'mst_mahasiswa' 
                  AND TYPE = 'U') 


6. Hindari mempergunakan SQL Cursors 
Cursor menggunakan banyak resource dalam memposisikan record dalam record set dan ini sangat mengurangi performa. Jika kita ingin memproses record satu persatu dalam loop, kita bisa menggunakan kalusa WHILE. Contoh :


DECLARE @temp_table TABLE (
  id        INT IDENTITY(1, 1),
  policy_no VARCHAR(50))
DECLARE @counter INT
DECLARE @max_record INT

INSERT INTO @temp_table
            (policy_no)
SELECT TOP 10 pr_policyno
FROM   mst_rule

SELECT @max_record = COUNT(*)
FROM   @temp_table

SET @counter=1

WHILE @counter <= @max_record
  BEGIN
      SELECT *
      FROM   @temp_table
      WHERE  id = @counter

      SET @counter=@counter + 1
  END 


7. Usahakan membuat transaksi sependek mungkin
Jika suatu transaksi memiliki kemampuan untuk mengunci suatu object maka dengan transaksi yang pendek, waktu lock pada suatu object di database akan pendek. Semakin panjang waktu transaksi, maka waktu lock pada suatu object juga akan semakin panjang. Dalam beberapa kasus, lock juga bisa menimbulkan deathlock.


8. Kembalikanlah integer value sebagai return value daripada mengembalikan integer yang merupakan bagian record set.
Menggunakan statement return integer akan mempercepat proses karena SQL tidak perlu membuat record set untuk dikembalikan ke client.


Semoga beberapa tips berikut membantu teman-teman dalam pembuatan stored procedure yang lebih cepat. ^_^