The Greatness of Local Variables in SQL Server Stored Procedure

Based on my experience, the problems in SQL Server database with a very large amount of data (Hugh Data), retrieval of data using the Stored Procedure with parameters will cause new problems, namely on the side of speed performance; This is because it is not done in a proper way of using the available elements; let's try to prove it!

In explaining this evidence, I will use the inverted way; I'll give you an example SQL Stored Procedure with the SELECT clause to retrieve data from multiple tables JOIN following parameters are used; The next compare in case the task of the Parameters replaced by Local Variables and see the results, it is remarkable - it would be the apparent if you are using large database.

Local Variable

The use of Local Variables in SQL Server, the value used in the query will be considered as a constant; so that SQL Server works simply by taking the data value of the Local Variable and will process the query using the data input in the form of constants.

In this example I am using SQL Server 2005 and I use a database that is already available, namely "AdventureWorksDW", let us consider the example query on the following stored procedure:

CODE
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

Run Stored Procedures in the Query tools with the command:
SP EXECUTE WITH TEST PARAMETERS '01 / 01/2001 ', '01 / 01/2005'.

Compare with Stored Procedure as follows:

CODE
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

Run the Query tools with the command:
EXECUTE SP_TEST_NO_PARAMETER

The latest Stored Procedure faster performance than the previous Stored Procedures, although the data are taken (recordset) is the same.

Now the problem is, in the Stored Procedure we use a guideline that will be used as a query request, the InvoiceID and Itemid; so we have to use the parameter. This problem is solved by using the Local Variable (as described above).

Add Variable Local and define the parameters passed into it, then the next you will get the extraordinary performance; as the following example:

CODE
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