Optimize sql query

Devendra Kumar

6y1k

Hi everone

I had creating a billing application and needs to calculate the balance qty,

I am having 5 tables

stock
purchase
PurchaseReturn
Sale
SalesReturn

I want the balanceQty (based on ItemCode, which is common) in stock table and for that I had created a scalar function and used it as formula of computed column, now the issue is the number of rows is getting huge (approx 5000 as of now) and it is taking much time to return the rows more than 2 minutes.

Function

ALTER FUNCTION [dbo].[GetClosingStock]
(
--Add the parameters for the function here
@ItemCode nvarchar(max)
)
RETURNS decimal(18, 3)
AS
BEGIN
--Declare the return variable here
DECLARE @sQty decimal(18, 3), @pQty decimal(18, 3), @prQty decimal(18, 3), @saleQty decimal(18, 3), @srQty decimal(18, 3)
DECLARE @Result decimal(18, 3)
-- Add the T - SQL statements to compute the return value here
Select @sQty = (select ISNULL(sum(qty), 0) from Stock where ItemCode = @ItemCode)
Select @pQty = (select ISNULL(sum(qty), 0) from Purchase where ItemCode = @ItemCode)
select @prQty = (select ISNULL(sum(qty), 0) from PurchaseReturn where ItemCode = @ItemCode)
select @saleQty = (select ISNULL(sum(qty), 0) from Sale where ItemCode = @ItemCode)
select @srQty = (select ISNULL(sum(qty), 0) from SalesReturn where ItemCode = @ItemCode)
select @Result = (@sQty + (@pQty - @prQty) - (@saleQty - @srQty))
-- Return the result of the function
RETURN @Result
END;

How can I optimize it to perform better or guide me to calculate the ClosingStockQty in a much better way.

Does StoreProcedure helps me out or anything

Thanks

Devendra

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta6y ago
Accepted answer
Hey

Fetchig data from multiple tables (especially in computed columns takes times to execute) as it calculates everytime when you fetch it. Alternatively you can try the following steps that might gives you efficient result
Instead of using computed column, use the persisted value and fill the value in your column
Whenever there is a change in your table (with that item code, just call your function or SP and fill the qty (Using triggers or some logic)
You will definitely get some better results in s a short span of time.

Good Luck
+3
Devendra Kumar6y ago
Thank you so much amit sir, I applied trugger and it works great.thank you so vey much
+1
Devendra Kumar6y ago
@Jaimin

Here is the query that I had tried with joins but only getting a benifit of 2-3 seconds
ALTER FUNCTION [dbo].[GetClosingStockNEW]
(
--Add the parameters for the function here
  @ItemCode nvarchar(100)
)
RETURNS decimal(18, 3)
AS
BEGIN

DECLARE @Result decimal(18, 3)

set @Result=
( select  balance =  (Qty + (PQty - PRQty) - (SQty - SRQty))
from
(
SELECT        Stock.ItemCode, SUM(ISNULL(Stock.Qty,0)) AS Qty,
                              SUM(ISNULL(Purchase.Qty,0)) AS PQty,
                              SUM(ISNULL(PurchaseReturn.Qty,0)) AS PRQty,
                              SUM(ISNULL(Sale.Qty,0)) AS SQty,
                              SUM(ISNULL(SalesReturn.Qty,0)) AS SRQty
FROM            Stock  LEFT  JOIN
                         Purchase ON Stock.ItemCode = Purchase.ItemCode LEFT JOIN
                         PurchaseReturn ON Stock.ItemCode = PurchaseReturn.ItemCode LEFT JOIN
                         Sale ON Stock.ItemCode = Sale.ItemCode LEFT JOIN
                         SalesReturn ON Stock.ItemCode = SalesReturn.ItemCode

WHERE        (Stock.ItemCode = @ItemCode)
group by Stock.ItemCode) a
)
--select @Result
--------------

-- Return the result of the function
    RETURN @Result
END;
Please share the query to get the results within seconds
+1
Jaimin Shethiya6y ago
Also can you share you tried with join query so will check and let you know if something needed change.
+1
Jaimin Shethiya6y ago
Hello Devendra,

can you tried with temp table store the data whatever you want and after you calculate your closing stock.

or make a new table create in SQL side and that table store only qty related data and whenever you want then use this table data.
+1
Devendra Kumar6y ago
Thanks Jaimin I tried with joins the difference is just 2-3 seconds. Is there any way to get it within seconds or best possible practice
+1
Jaimin Shethiya6y ago
Hello Devendra,

can you please check with joins in above query Itemcode is common so you can join with that column and use total of the all the qty.

Thanks
+1