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 menurunakn 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 Store 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 EXISTS (SELECT 1) Menggantikan (SELECT * atau SELECT NamaKolom)
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 perofrma yang baik untuk operasi SET.
Komentar
Posting Komentar