Speed up SQL Stored Procedure by changing IF...ELSE Block and Loop with SQL Queries

Introduction

In this article I am explaining how to speed up SQL stored procedure by changing the way of writing logic. Normally the stored procedure contains Loops, CURSOR and IF....ELSE will execute fast if the number of records are less, but when the number records are more then the execution speed will comes down. So we have to change the way of writing stored procedures by avoiding Loops, CURSOR and IF...ELSE statements by SQL Queries.

Using the code

In order to understand the difference between the normal way of writing logic and SQL Queries model I have explained a small example bellow.

We have the following tables

The CustomerMaster table having the details of the customer; CustomerName, DiscountPercentage Customer belongs to GoldCustomer or not etc...

The PurchaseTransactions Table will have the Purchase made by customer.

We are going to write a stored procedure for generating the Bill of the customer by refer PurchaseTransactions and CustomerMaster.

DiscountPercentage field in the CustomerMaster Table will have the discount percentage information for the particular customer, each customer have different DiscountPercentage values. One more additional conditions is If the customer belongs to Gold Customer (GoldCustomer - bit field in CustomerMaster) then additional 2% discount will be given to the particular customer.

First we will write the procedure using Cursor, IF...ELSE and other normal Set statements as follows...

DECLARE @CustomerID int;
DECLARE @GoldCustomer bit;
DECLARE @DiscountPercentage float;
DECLARE @TotalAmt float;
DECLARE @DiscountAmt float;

DECLARE @NewBillID int;
DECLARE @PurchaseID int;
DECLARE @PurchaseAmt float
DECLARE @SlNo int;

--Open the CustomerMaster Table and Read each record by using curser - Cur
DECLARE Cur CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT CustomerID, GoldCustomer FROM CustomerMaster ORDER BY CustomerID
OPEN Cur
FETCH NEXT FROM Cur INTO @CustomerID, @GoldCustomer
WHILE @@FETCH_STATUS = 0
    BEGIN
        --Store Discount percentage of the customer into @DiscountPercentage
        Select @DiscountPercentage = DiscountPercentage 
        From CustomerMaster Where CustomerID = @CustomerID;
        
        --Calculate the total BillAmount and Store in to @TotalBillAmt
        Select @TotalAmt = SUM(PurchaseAmt) From PurchaseTransactions Where CustomerID = @CustomerID;
        
        --Calculate the Discount
        --IF the Customer is Gold Customer Additional 2% Discount has to be given
        IF @GoldCustomer = 1
            Begin
                Select @DiscountAmt = ( (@TotalAmt * (@DiscountPercentage + 2) ) / 100);
            End
        Else
            Begin
                Select @DiscountAmt = ( (@TotalAmt * @DiscountPercentage) / 100);
            End

        --Insert the Details into Bill Master
        Insert INTO BillMaster (CustomerID, TotalAmt, DiscountAmt, NetAmt)
        VALUES (@CustomerID, @TotalAmt, @DiscountAmt, @TotalAmt - @DiscountAmt)

        --Get the newly inserted BillID into @NewBillID
        Select @NewBillID = @@identity;

        ----------------------------------------------------------------------------------
        --Insert Purchase details into Bill Details table by calculating each item discount
        --Start New Cur
        Set @SlNo = 1;
        
        DECLARE CurPurchase CURSOR FAST_FORWARD READ_ONLY FOR 
        SELECT PurchaseID, PurchaseAmt FROM PurchaseTransactions 
        Where CustomerID = @CustomerID ORDER BY PurchaseDate
        
        OPEN CurPurchase
        FETCH NEXT FROM CurPurchase INTO @PurchaseID, @PurchaseAmt
        WHILE @@FETCH_STATUS = 0
            BEGIN
                --Calcuate the discount amount of the item
                --IF the Customer is Gold Customer Additional 2% Discount has to be given
                IF @GoldCustomer = 1
                    Begin
                        Select @DiscountAmt = ( (@PurchaseAmt * (@DiscountPercentage + 2) ) / 100);
                    End
                Else
                    Begin
                        Select @DiscountAmt = ( (@PurchaseAmt * @DiscountPercentage) / 100);
                    End

                --Insert the details into bill details table
                Insert INTO BillDetails( BillID, SlNo, PurchaseID, Amt, DiscountAmt, NetAmt)
                Values (@NewBillID, @SlNo, @PurchaseID, @PurchaseAmt, @DiscountAmt, 
                        @PurchaseAmt - @DiscountAmt);
                
                Set @SlNo = @SlNo + 1;
                FETCH NEXT FROM CurPurchase INTO @PurchaseID, @PurchaseAmt
            END
        CLOSE CurPurchase
        DEALLOCATE CurPurchase
        ----------------------------------------------------------------------------------
        
        FETCH NEXT FROM Cur INTO @CustomerID, @GoldCustomer
    END
CLOSE CUR
DEALLOCATE CUR

The above SQL Statements will works fine and fast with less number of customer. But when the number of customers increases then more number of SQL Queries/Statements will fire and the database server becomes heavy loaded. and it may take minutes or even hours to complete.

Next we are going to re write the above logic by using only Two SQL Statements as follows. Here while executing the SQL itself the calculations will be taken care by properly matching records. Here it will take only few seconds to complete the execution irrespective of the number of records effected.

 --Inserting Bill Master
Insert Into BillMaster (CustomerID, TotalAmt, DiscountAmt, NetAmt)
Select 
PurchaseTransactions.CustomerID, 
SUM(PurchaseTransactions.PurchaseAmt) As TotalAmt, 
SUM
(
(PurchaseTransactions.PurchaseAmt * 
((Case CustomerMaster.GoldCustomer When 1 THEN 2 ELSE 0 End) +
CustomerMaster.DiscountPercentage)) / 100
) As DiscountAmt,
SUM(PurchaseTransactions.PurchaseAmt) -
SUM
(
(PurchaseTransactions.PurchaseAmt * 
((Case CustomerMaster.GoldCustomer When 1 THEN 2 ELSE 0 End) +
CustomerMaster.DiscountPercentage)) / 100
) As NetAmt 
From PurchaseTransactions Inner JOIN CustomerMaster
ON PurchaseTransactions.CustomerID = CustomerMaster.CustomerID
GROUP BY PurchaseTransactions.CustomerID

--Insert Bill Details
Insert Into BillDetails(BillID, SlNo, PurchaseID, Amt, DiscountAmt, NetAmt)
SELECT 
BillMaster.BillID, 
ROW_NUMBER() OVER (Partition By PurchaseTransactions.CustomerID ORDER BY PurchaseDate) As SlNo,
PurchaseTransactions.PurchaseID,
PurchaseTransactions.PurchaseAmt,
((PurchaseTransactions.PurchaseAmt * 
((Case CustomerMaster.GoldCustomer WHEN 1 Then 2 ELSE 0 END) +
CustomerMaster.DiscountPercentage)
) / 100) As DiscountAmt,
PurchaseTransactions.PurchaseAmt - 
((PurchaseTransactions.PurchaseAmt * 
((Case CustomerMaster.GoldCustomer WHEN 1 Then 2 ELSE 0 END) +
CustomerMaster.DiscountPercentage) 
) / 100) As NetAmt
FROM PurchaseTransactions Inner JOIN CustomerMaster 
ON PurchaseTransactions.CustomerID = CustomerMaster.CustomerID
INNER JOIN BillMaster 
ON PurchaseTransactions.CustomerID = BillMaster.CustomerID

Points of Interest

We can Implement any type of complex logic by using only SQL Queries. The only thing is we have design the database according to the logic.