Cara mempercepat proses query di MS SQL Server

Bagi sebagian diantara kita, berurusan dengan query adalah hal yang pasti dilakukan setiap hari. Kadang performa query kita terasa begitu lambat, apalagi ketika berhadapan dengan data yang sangat besar. Mungkin kita melewatkan hal-hal kecil yang dapat berpengaruh besar terhadap performa query tersebut. Bayangkan jika query itu berupa stored procedure yang akan terus digunakan dalam aplikasi.

Berikut ini 10 tips meningkatkan performa query (stored procedure) yang kita buat:

• Gunakan NOCOUNT 

SQL Server secara default memberikan informasi tentang berapa row data yang terpengaruh dari query kita (177 row(s) affected). Jika kita tidak memerlukan informasi ini, matikan dengan SET NOCOUNT ON. Hal ini akan mengurangi network traffic yang digunakan untuk pengiriman informasi tersebut.
CREATE PROC dbo.NamaProc
AS
SET NOCOUNT ON
–Isi Procedure

• Akses tabel dengan NOLOCK 

Kebanyakan akses tabel tidak membutuhkan fitur ini. Matikan dengan WITH (NOLOCK) setelah nama tabel. NOLOCK dalam query select tidak hanya mengabaikan Exclusive Lock namun juga Shared Lock sehingga tidak akan ada delay ketika proses lain melakukan write ke tabel yang sedang kita read.
CREATE PROC dbo.NamaProc
AS
SET NOCOUNT ON
SELECT Id, [Name]
FROM NamaTabel WITH (NOLOCK)

• Hindari penggunaan or dalam where

Gunakan IF ELSE menggantikan OR. Misalnya ketika kita membuat fungsi search yang parameternya fleksibel, dalam contoh ini email dan name, dimana salah satu parameter pasti null. SELECT Id FROM NamaTabel WHERE Email = @Email OR Name = @Name Menjadi

IF @Email IS NULL SELECT Id FROM NamaTabel WHERE [Name] = @Name ELSE SELECT Id FROM NamaTabel WHERE Email = @Email

• Sesuaikan tipe data 

   Kita bisa saja menjalankan query walaupun tipe data 

   parameter dengan field pada tabel berbeda, namun hal ini  

   akan menurunkan performa query kita karena SQL Server akan 

   terlebih dahulu mengkonversi tipe data agar tersebut 

   sama.Misalnya tipe data Id pada tabel NamaTabel adalah 

   Integer.

   CREATE PROC dbo.NamaProc
   @Id VARCHAR(20)
   AS
   SELECT [Name], Email FROM NamaTabel
   WHERE Id = @Id
 

• Gunakan penulisan Nama Objek secara lengkap 

Menuliskan nama objek secara lengkap (server.database.schema.objectName) membantu SQL Server langsung menuju ke objek yang kita maksud tanpa perlu melakukan pencarian dan meningkatkan peluang penggunaan kembali (reuse) execution plan yang sudah ada jika objek sudah pernah digunakan.
SELECT Id, [Name] FROM DBName.dbo.NamaTabel
EXEC DBName.dbo.NamaProc

• Kurangi pemanggilan GETDATE(), gunakan variabel 

Jika kita membuat sebuah stored procedure yang banyak memanggil fungsi GETDATE(), gantikan fungsi-fungsi tersebut dengan sebuah variabel date yang kita assign dengan GETDATE() sehingga pemanggilan fungsi tersebut tidak perlu dilakukan berulang-ulang.
DECLARE @Date DATETIME
SET @Date = GETDATE()

• Jangan menggunakan awalan "SP_" untuk nama procedure

Jika sebuah stored procedure diawali dengan “SP_” ketika eksekusi SQL Server akan terlebih dahulu mencari objek tersebut dalam System Stored Procedures, hal ini akan menurunkan performa dan bahkan menghasilkan hasil yang salah jika nama stored procedurenya sama.  

• Gunakan IF EXIST(SELECT 1) menggantikan (SELECT * atau SELECT nama kolom)

Untuk mengecek keberadaan data pada suatu tabel kita sering menggunakan IF EXISTS. IF EXISTS akan mengembalikan nilai true jika statement kondisinya mengembalikan nilai apapun, baik itu “1″ ataupun semua kolom, dan semua hasil itu tidak digunakan. Jadi, untuk mengurangi network traffic dan mengurangi pemrosesan data, gunakanalah “1″ daripada data pada tabel.
IF EXISTS (SELECT * FROM NamaTabel WHERE Id = @Id) atau IF EXISTS(SELECT Id FROM NamaTabel WHERE Id = @Id)
Menjadi
IF EXISTS (SELECT 1 FROM NamaTabel WHERE Id = @Id)

• Gunakan sp_executesql menggantikan EXECUTE

Jika kita menggunakan sp_executesql untuk mengeksekusi query berkali-kali SQL Server akan menggunakan kembali (reuse) execution plan yang sudah ada, hal ini akan menigkatkan performa. Execution plan hanya dapat digunakan kembali jika setiap karakter dari query sama persis.

DECLARE @Query VARCHAR(MAX)

DECLARE @Id INT

SET @Id = 7

SET @Query = ‘SELECT * FROM dbo.NamaTabel WHERE Id = ‘ +CONVERT(VARCHAR(10),@Id) EXEC (@Query)  

Pada contoh di atas ketika parameter @Id berubah, execution plan pada saat @Id = 7 tidak dapat digunakan kembali.  

Jika kita ubah menjadi:

DECLARE @Query VARCHAR(MAX)

SET @Query = ‘SELECT * FROM dbo.tblPerson WHERE Id = @Id‘

EXECUTE sp_executesql @Query, ‘@Id INT‘, @Id = 7  

Perubahan parameter tidak mempengaruhi SELECT statement sehingga execution plan dapat terus digunakan kembali walaupun parameter berubah.

• Hindari penggunaan cursor 

Cursor memakan banyak resource untuk me-maintain posisinya saat proses, hal ini menurunkan performa query kita. Usahakanlah mengganti penggunaan cursor dengan WHILE dan SET. SQL Server memiliki performa yang baik untuk operasi SET.  

Sumber :

http://students.netindonesia.net/blogs/herwinlesmana/archive/2011/12/25/1830.aspx