Query performanceRSS

• • Reply
  jsshivalik

  Member

  122 Points

  441 Posts

  Query performance

  Nov 28, 2019 10:50 AM|jsshivalik|LINK

  Hi

    I have below Stored Procedure to get the below desired result. Is there any best way without using Cursor

  Alter PROCEDURE [dbo].[Sp] 
  	@frDate datetime,
  	@toDate datetime
  AS
  BEGIN
  	SET NOCOUNT ON;
  
      CREATE TABLE [#Gl] (IdInt int PRIMARY KEY IDENTITY,
          LineType int,
  		AcctCode  nvarchar(15),
  		AcctName  nvarchar(100),
  		
  		DocumentDate  datetime,  
  		Debit  numeric(19,6),
  		Credit  numeric(19,6),
  		Balance  numeric(19,6),
  		Dr_Cr nvarchar(2)
  	)
  
  	DECLARE @AcctCode VARCHAR(15);
  	Declare @AcctName VARCHAR(100);
  	DECLARE @DocumentDate datetime;
  	DECLARE @Debit decimal(18,2);
  	DECLARE @Credit decimal(18,2);
  	DECLARE @Balance decimal(18,2);
  	DECLARE @Dr_Cr VARCHAR(2);
  	DECLARE @Dr_Opg decimal(18,2);
  	DECLARE @Cr_Opg decimal(18,2);
  	DECLARE @Dr_Trans decimal(18,2);
  	DECLARE @Cr_Trans decimal(18,2);
  
  	DECLARE Account_Cursor CURSOR local FOR 
      SELECT AcctCode	, AcctName FROM tbl1  order by AcctCode
  
  	OPEN Account_Cursor
  	FETCH NEXT FROM Account_Cursor INTO
  		@AcctCode, @AcctName
  
  	WHILE @@FETCH_STATUS = 0
  	BEGIN
  		Set @Debit = 0;
  		Set @Dr_Opg = 0;
  		Set @Credit = 0;
  		Set @Cr_Opg = 0;
  		Set @Balance = 0;
  		Set @Dr_Cr = '';
  		set @Narration = '';
  
  		select  @Dr_Opg = isnull(Sum(Debit),0) , @Cr_Opg = isnull(sum(Credit),0)  from [dbo].[JDT1]tbl2
  		Where Account = @AcctCode and RefDate < Cast(@frDate AS DATE)
  		if (@Dr_Opg) >  (@Cr_Opg)
          begin
  			select @Debit = (@Dr_Opg - @Cr_Opg)
  			select @Credit =  0
  			select @Dr_Opg = @Debit
  			select @Dr_Cr = 'Dr'
  			Set @Dr_Trans += @Dr_Opg;
  			set @Balance = @Dr_Opg;
          End
          if (@Dr_Opg) <  (@Cr_Opg)
          begin
  			select @Credit = (@Cr_Opg - @Dr_Opg)
  			select @Debit =  0
  			select @Cr_Opg = @Credit
  			select @Dr_Cr = 'Cr'
  			Set @Cr_Trans += @Cr_Opg;
  			set @Balance = @Cr_Opg;
  		End
  
  		INSERT into #Gl ([AcctCode], [DocumentNo], [DocumentDate],[Narration], [Debit], [Credit], [Balance], [Dr_Cr])
  			Values (@AcctCode,'',NULL,'Opg. Balance',@Debit,@Credit,@Balance,@Dr_Cr)
  
  		INSERT into #Gl ([AcctCode],[DocumentNo], [DocumentDate], [Debit], [Credit])
  		select 
  		T1.Account,'',
  		 T1.TransId,T1.RefDate,
  		 
  			 T1.Debit,T1.Credit
  		 from jdt1 T1 
  		 where (T1.Debit <> 0.0 or T1.Credit <> 0.0)
  			AND T1.Account = @AcctCode
  			And T1.refDate between Cast(@frDate AS DATE) And Cast(@toDate AS DATE)
  		order by T1.Account,T1.RefDate
  
  		FETCH NEXT FROM Account_Cursor INTO
  		@AcctCode, @AcctName
  	END
  	CLOSE Account_Cursor
  	DEALLOCATE Account_Cursor
  
  	select * from [#Gl]
  
  End
  
  
  tbl1	AcctCode	AcctName				
  	C001	ABC				
  	C002	XYZ				
  	C003	DEF				
  						
  tbl2	Date	    AcctCode	Debit	Credit	Balance	
  	31-03-2019	C001	1000		1000	Dr
  	05-05-2019	C001	5000		6000	Dr
  	25-05-2019	C001		2000	4000	Dr
  	30-06-2019	C001		1000	3000	Dr
  	10-04-2019	C002		40000	40000	Cr
  	01-05-2019	C002		20000	60000	Cr
  	30-06-2019	C002	70000		10000	Dr
  
  Below is the output 
  
  Date From 10/05/2019 to 30/06/2019					
  					
  					
  		       Debit	Credit	Balance	
  Opg Balance		6000		6000	Dr
  25-05-2019	C001		2000	4000	Dr
  30-06-2019	C001		1000	3000	Dr
  					
  Opg Balance			60000	60000	Cr
  30-06-2019	C002	70000		10000	Dr
  

  Thanks

• • Reply
  Yang Shen

  Contributor

  2270 Points

  735 Posts

  Re: Query performance

  Nov 29, 2019 09:00 AM|Yang Shen|LINK

  Hi jsshivalik,

  The procedure seems does not match with the tables and result table. I used the code provided and modified places where conflict with your current provided tables and it won't out put the result which makes it hard to understand the job your SP did about the tables. Also, the result set is confusing and can't see the rule you get this result. With these problems and confusion, it's hard to provide a specific solution for yout.

  However, you can try to use CTE with recursion in sql ot replace the cursors. 

  You can refer to SQL Server Recursive CTE see if you can get rid of the cursors by CTE.

  Best Regard,

  Yang Shen