Kehebatan Local Variabel Saat Mengganti Tugas Parameters di Store Procedure SQL Server

Berdasarkan pengalaman saya, permasahalan pada Database SQL Server dengan jumlah data yang sangat besar (Hugh Data), pengambilan data mennggunakan Stored Procedure dengan Parameternya akan menimbulkan masalah baru, yaitu pada sisi performa kecepatan; Hal ini karena tidak dilakukan dengan cara yang tepat dalam menggunakan unsur yang tersedia; mari kita coba buktikan!

Dalam menjelaskan pembuktian ini, saya akan menggunakan cara terbalik; saya akan memberikan contoh SQL Stored Procedure dengan Clause SELECT untuk mengambil data dari 2 tabel JOIN berikut parameter yang digunakan; selanjutnya bandingkan jika seandainya tugas dari Parameters tersebut di ganti oleh Local Variables dan lihat hasilnya, sangat luar biasa -- akan lebih terlihat dengan nyata jika anda menggunakan database besar.

Parameter

Pada saat pertama kali sebuah Stored Procedure di Execute, SQL akan melihat data input Parameter akan digunakan sebagai pedoman dalam merencanakan sebuah permintaan pada Query yang digunakan. SQL Server akan selalu mengambil data input Peremeter tersebut setiap baris proses yang hasilkan (baik berfungsi sebagai data input atau sebagai filter sebuah pencarian Recordset), sehingga jika data yang ada pada database tersebut besar dan filter pencarian komplek akan mengakibatkan kinerja akan melambat.

Local Variabel

Penggunaan Local variabel dalam SQL Server, nilai yang digunakan dalam Query akan di anggap seperti sebuah konstanta; sehingga pekerjaan SQL Server cukup dilakukan dalam mengambil data nilai dari Local Variabel dan akan memproses query tersebut seolah-olah menggunakan sebuah data input berupa konstanta.

Dalam contoh ini saya menggunakan SQL Server 2005 dan saya gunakan database yang telah tersedia yaitu "AdventureWorksDW", mari kita perhatikan contoh query pada stored procedure berikut ini:

USE [AdventureWorksDW]
CREATE PROCEDURE [dbo].[SP_TEST_WITH_PARAMETER]
@FullDateAlternateKey1 varchar(10),
@FullDateAlternateKey2 varchar(10)
AS
BEGIN
SELECT * FROM FactInternetSales RS
INNER JOIN DimTime DT ON RS.DueDateKey=DT.TimeKey
INNER JOIN DimCustomer C ON RS.CustomerKey=C.CustomerKey
INNER JOIN DimGeography DG ON C.GeographyKey=DG.GeographyKey
INNER JOIN DimSalesTerritory ST ON DG.SalesTerritoryKey=ST.SalesTerritoryKey
INNER JOIN DimProduct P ON RS.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory SC ON P.ProductSubcategoryKey=SC.ProductSubcategoryKey
INNER JOIN DimProductCategory PC ON SC.ProductCategoryKey= PC.ProductCategoryKey
WHERE FullDateAlternateKey BETWEEN @FullDateAlternateKey1 AND @FullDateAlternateKey2
END

Jalankan Stored Procedure diatas dalam Query tools dengan perintah:
EXECUTE SP_TEST_WITH_PARAMETER '01/01/2001', '01/01/2005'.

Bandingkan dengan Stored Procedure seperti berikut ini:

USE [AdventureWorksDW]
PROCEDURE [dbo].[SP_TEST_NO_PARAMETER]
AS
BEGIN
  SELECT * FROM FactInternetSales RS
  INNER JOIN DimTime DT ON RS.DueDateKey=DT.TimeKey
  INNER JOIN DimCustomer C ON RS.CustomerKey=C.CustomerKey
  INNER JOIN DimGeography DG ON C.GeographyKey=DG.GeographyKey
  INNER JOIN DimSalesTerritory ST ON DG.SalesTerritoryKey=ST.SalesTerritoryKey
  INNER JOIN DimProduct P ON RS.ProductKey=P.ProductKey
  INNER JOIN DimProductSubcategory SC ON P.ProductSubcategoryKey=SC.ProductSubcategoryKey
  INNER JOIN DimProductCategory PC ON SC.ProductCategoryKey= PC.ProductCategoryKey
  WHERE FullDateAlternateKey BETWEEN '01/01/2001' AND '01/01/2005'
END

Jalankan dalam Query tools dengan perintah:
EXECUTE SP_TEST_NO_PARAMETER

Maka Stored Procedure yang terakhir yang akan lebih cepat performance nya dibanding Stored Procedure diatasnya, walaupun data yang diambil (recordset) adalah sama.

Nah sekarang masalahnya adalah, dalam Stored Procedure tersebut kita menggunakan sebuah pedoman yang akan digunakan sebagai permintaan Query, yaitu InvoiceID dan ItemID; sehingga kita harus menggunakan Parameter. Permasalahan ini terpecahkan dengan cara menggunakan Local Variable (seperti dijelaskan di atas).

Tambahkan Local Variable dan definisikan Parameter yang dilewatkan kedalamnya, maka selanjutnya anda akan mendapatkan hasil yang sangat luar biasa performanya; seperti contoh berikut ini :

USE [AdventureWorksDW]
CREATE PROCEDURE [dbo].[SP_TEST_WITH_LOCAL_VARIABLE]

@FullDateAlternateKey1 varchar(10),
@FullDateAlternateKey2 varchar(10)
AS
SET NOCOUNT ON
DECLARE @LOCAL_FullDateAlternateKey1 varchar(10)
DECLARE @LOCAL_FullDateAlternateKey2 varchar(10)
SET @LOCAL_FullDateAlternateKey1=@FullDateAlternateKey1
SET @LOCAL_FullDateAlternateKey2=@FullDateAlternateKey2
BEGIN
SELECT * FROM FactInternetSales RS
  INNER JOIN DimTime DT ON RS.DueDateKey=DT.TimeKey
  INNER JOIN DimCustomer C ON RS.CustomerKey=C.CustomerKey
  INNER JOIN DimGeography DG ON C.GeographyKey=DG.GeographyKey
  INNER JOIN DimSalesTerritory ST ON DG.SalesTerritoryKey=ST.SalesTerritoryKey
  INNER JOIN DimProduct P ON RS.ProductKey=P.ProductKey
  INNER JOIN DimProductSubcategory SC ON P.ProductSubcategoryKey=SC.ProductSubcategoryKey
  INNER JOIN DimProductCategory PC ON SC.ProductCategoryKey= PC.ProductCategoryKey
  WHERE FullDateAlternateKey BETWEEN @LOCAL_FullDateAlternateKey1 AND @LOCAL_FullDateAlternateKey2
END

SET NOCOUNT 

SET NOCOUNT {ON | OFF} adalah setting dalam sebuah Query Clause SELECT yang berfungsi untuk mengembalikan nilai balik berpa jumlah baris Recordset yang dihasilkan oleh Stored Procedure; When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned. Jika status ON, maka tentu performance Query lebih cepat; tetapi itu semua tergantung dari kebutuhan sistem, apakah memang memerlukan nilai balik jumlah baris Recordset atau tidak.

Pada artikel ini saya telah membuktikan dan berdasarkan pengalaman saya saat pekerjaan saya sebagai programmer yang mengharuskan bisa mengoptimalkan pengelolaan data yang terus membesar; saran saya, jangan meremehkan penulisan Query yang benar walaupun data kecil, karena suatu saat itulah yang akan memberi masalah kepada anda; saya harap artikel ini bermanfaat.