#storedprocedure

Dica simples para executar stored procedure via comando no SQL Server.

Para executar uma stored procedure no SQL Server faça o seguinte: EXEC spMinhaStoredProcedure @parametro1 = '2', @parametrodata = '2019/03/19';

오랜 만에 스토어드 프로시저를 사용하려니

자주 사용하지는 않지만 Stored Procedure라는 게 있다. 보통은 누구에게 프로그램을 뿌릴 일이 없어 걍 쿼리를 문자열로 만들어 디비에 던져 두었는데, 이번엔 혼자 사용하는 게 아니라서 쿼리를 하드코딩하기에 좀 부담스럽다. 특히 요구조건이 변덕스럽게 바뀐다면 일일이 하드코딩한 쿼리를 고쳐줄 수 없다. 그래서 이번엔 ‘Stored Procedure’라는 걸 사용해 보려고 한다. 막상 사용해보려니 sp를 만드는 것부터 시작하여 이를 이용하여 레코드셋을 가져오는 일까지 가물가물하다. stackoverflow의 도움을 받아 스토어드 프로시저를 이용하여 레코드셋을 가져오는 데모를 만들어 보았다. 참고로 이게 뭐냐 하시는 분들을 위해 간단히 설명하자면 디비에 쿼리를 저장해두고 매개변수를 넘겨주어(경우에…

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.

Öncelikle buraya tıklayarak JDBC Driver 4.0 dosyamızı indirelim. Daha sonra bunu projemize ekledikten sonra aşağıdaki kodları adım adım uygulayalım.