Devendra Kumar

Devendra Kumar

  • 1.5k
  • 432
  • 89.9k

Optimize sql query

Sep 19 2020 10:37 AM
Hi everone
 
I had creating a billing application and needs to calculate the balance qty,
I am having 5 tables
  1. stock
  2. purchase
  3. PurchaseReturn 
  4. Sale
  5. 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
  1. ALTER FUNCTION [dbo].[GetClosingStock]   
  2. (   
  3. --Add the parameters for the function here   
  4.  @ItemCode nvarchar(max)   
  5. )   
  6. RETURNS decimal(18, 3)   
  7. AS   
  8. BEGIN   
  9.   
  10. --Declare the return variable here   
  11. DECLARE @sQty decimal(18, 3), @pQty decimal(18, 3), @prQty decimal(18, 3), @saleQty decimal(18, 3), @srQty decimal(18, 3)   
  12.   
  13. DECLARE @Result decimal(18, 3)   
  14. -- Add the T - SQL statements to compute the return value here   
  15.   
  16. Select @sQty = (select  ISNULL(sum(qty), 0) from Stock where ItemCode = @ItemCode)    
  17. Select @pQty = (select ISNULL(sum(qty), 0) from Purchase where ItemCode = @ItemCode)   
  18. select @prQty = (select ISNULL(sum(qty), 0) from PurchaseReturn where ItemCode = @ItemCode)   
  19. select @saleQty = (select ISNULL(sum(qty), 0) from Sale where ItemCode = @ItemCode)   
  20. select @srQty = (select ISNULL(sum(qty), 0) from SalesReturn where ItemCode = @ItemCode)   
  21.   
  22. select @Result = (@sQty + (@pQty - @prQty) - (@saleQty - @srQty))   
  23.   
  24. -- Return the result of the function    
  25.    RETURN @Result   
  26. 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 

Brought to you by:

Answers (7)