Optimize sql query

Question

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

Amit Gupta · 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

Jaimin Shethiya · Answer

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

Devendra Kumar · Answer

Thank you so much amit sir, I applied trugger and it works great.thank you so vey much

Devendra Kumar · Answer

@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

Jaimin Shethiya · Answer

Also can you share you tried with join query so will check and let you know if something needed change.

Jaimin Shethiya · Answer

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.

Devendra Kumar · Answer

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