How to get quick data fast in SQL server

Question

1.00/5 (1 vote)

See more:

SQL

--Exec [DisplayFeeBillDescription] null,null,null,null,null,1,25,null,true,null
 
    
ALTER PROCEDURE [dbo].[DisplayFeeBillDescription]                   
 @InstallmentID INTEGER=NULL,                                
 @CategoryID INTEGER=NULL,                                
 @ShiftID INTEGER=NULL,                            
 @CourseID INTEGER=NULL,                            
 @SpecializationID INTEGER=NULL,                                
 @InstituteID INTEGER=NULL,                          
 @SessionID INTEGER=NULL,              
 @ActiveID BIT=NULL,
 @IsRegistered BIT,
 @ProgramID int=null                                    
AS                                
 SET NOCOUNT ON                           
 -- BilledAmount = Opening Trimester Balance                   
 DECLARE @result TABLE                    
 (                    
  ShiftID INT,                    
  CourseID INT,                    
  SpecializationID INT,                    
  Batch NVARCHAR(MAX),         
  SessionID INT,                      
  StudentID INT,                    
  AdmissionNumber NVARCHAR(MAX),                    
  StudentName NVARCHAR(MAX),             
  BilledAmount NUMERIC(18,2) ,             
  TrimesterBilledAmount NUMERIC(18,2),             
  PaidAmount NUMERIC(18,2) ,             
  Concession NUMERIC(18,2) ,                               
  OutstandingAmount NUMERIC(18,2),
  AdjustedAmount NUMERIC(18,2),
  PaymentAmount NUMERIC(18,2),
  TrimesterStudentID INT                   
 )                     
  DECLARE @misc TABLE                    
 (                    
  ShiftID INT,                    
  CourseID INT,                    
  SpecializationID INT,                    
  Batch NVARCHAR(MAX),                       
  SessionID INT,        
  StudentID INT,                    
  AdmissionNumber NVARCHAR(MAX),                    
  StudentName NVARCHAR(MAX),            
  BilledAmount NUMERIC(18,2) ,             
  TrimesterBilledAmount NUMERIC(18,2),             
  PaidAmount NUMERIC(18,2) ,             
  Concession NUMERIC(18,2) ,                                 
  OutstandingAmount NUMERIC(18,2),
  AdjustedAmount NUMERIC(18,2),
  PaymentAmount NUMERIC(18,2)                   
 )                      
                        
    --------------------------Student Details-----------------------------------------                  
                      
   Insert Into @result(ShiftID,CourseID,SpecializationID,Batch,SessionID,StudentID,AdmissionNumber,StudentName, 
   BilledAmount, PaidAmount ,Concession , OutstandingAmount, AdjustedAmount ,PaymentAmount ,TrimesterBilledAmount,TrimesterStudentID)                      
   SELECT DISTINCT SP.ShiftID,SP.CourseID,NULL,SH.Shift +'/'+CO.Course AS Batch,SP.SessionID,                       
   SP.StudentID,SP.AdmissionNumber,dbo.GetFullName(SP.FirstName,SP.MiddleName,SP.LastName)[StudentName],
   0 [BilledAmount],0 [PaidAmount] ,0 [Concession],0 [OutstandingAmount],0 [AdjustedAmount] ,0 [PaymentAmount],0 [TrimesterBilledAmount],TR.StudentID                                 
   FROM biz_StudentProfile SP(NOLOCK)                      
    INNER JOIN biz_Shift SH(NOLOCK) ON SP.ShiftID=SH.ShiftID                                    
    INNER JOIN biz_Course CO(NOLOCK) ON SP.CourseID=CO.CourseID  
    inner join biz_programcourse PG(NoLock) on PG.courseID=Co.courseid                                  
    --LEFT JOIN biz_Specialization SS(NOLOCK) ON SP.StreamID=SS.SpecializationID 
    LEFT JOIN biz_TrimesterRegistration TR(NOLOCK) ON TR.SessionID=SP.SessionID AND TR.StudentID=SP.StudentID
   WHERE (@CategoryID IS NULL OR SP.CategoryID=@CategoryID) AND                                
    (@ShiftID IS NULL OR SP.ShiftID=@ShiftID)  AND                                  
    (@CourseID IS NULL OR SP.CourseID=@CourseID)  AND                                  
    (@SpecializationID IS NULL OR SP.StreamID=@SpecializationID)  AND                             
    (@InstituteID IS NULL OR SP.InstituteID=@InstituteID) AND                            
    (@SessionID IS NULL OR SP.SessionID=@SessionID) AND                              
    (@ActiveID IS NULL OR SP.IsActive=@ActiveID) AND   
    (@IsRegistered=0 OR TR.StudentID IS NOT NULL)  and  
    (@ProgramID is Null OR pg.programID=@ProgramID)          
   order by AdmissionNumber                          
                        
                      
    ------------*************************************************************----------                     
    --------------------------Fee Bill ------------------------------------------------                        
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount, PaidAmount ,Concession, OutstandingAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,R.BilledAmount + SUM(ISNULL(FB.Amount,0))[BilledAmount], 
    R.PaidAmount ,R.Concession, R.OutstandingAmount + SUM(ISNULL(FB.Amount,0)) [OutstandingAmount]            
    FROM biz_FeeBill FB(NOLOCK)                        
    INNER JOIN biz_Installment IL(NOLOCK) ON FB.InstallmentID=IL.InstallmentID                   
    JOIN @result R  ON R.StudentID=FB.StudentID                     
    WHERE  (@SessionID IS NULL OR FB.SessionID <= @SessionID)                  
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.PaidAmount ,R.Concession,R.OutstandingAmount                  
                  
                         
                       
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     PaidAmount=M.PaidAmount,            
     Concession=M.Concession                
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   -----------------------******************************--------------------------------                       
           
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,TrimesterBilledAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    ISNULL(R.TrimesterBilledAmount,0) + SUM(ISNULL(FB.Amount,0))[TrimesterBilledAmount]              
    FROM biz_FeeBill FB(NOLOCK)                        
    INNER JOIN biz_Installment IL(NOLOCK) ON FB.InstallmentID=IL.InstallmentID                   
    JOIN @result R  ON R.StudentID=FB.StudentID AND FB.SessionID=R.SessionID        
    WHERE (@SessionID IS NULL OR FB.SessionID=@SessionID)      
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,R.TrimesterBilledAmount                  
                  
    UPDATE @result SET TrimesterBilledAmount=M.TrimesterBilledAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount, PaidAmount ,Concession,OutstandingAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount - SUM(ISNULL(FB.Amount,0)) [BilledAmount], R.PaidAmount [PaidAmount] ,R.Concession,          
    R.OutstandingAmount - SUM(ISNULL(FB.Amount,0)) [OutstandingAmount]                   
    FROM biz_FeeBill FB(NOLOCK)                        
    INNER JOIN biz_Installment IL(NOLOCK) ON FB.InstallmentID=IL.InstallmentID  AND ISNULL(FB.IsCancel,0)=1                        
    JOIN @result R  ON R.StudentID=FB.StudentID                     
    WHERE  (@SessionID IS NULL OR FB.SessionID <= @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.PaidAmount ,R.Concession,R.OutstandingAmount                   
            
            
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     PaidAmount=M.PaidAmount,            
     Concession=M.Concession                    
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   --------------------****************************************------------------------  
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,TrimesterBilledAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    ISNULL(R.TrimesterBilledAmount,0) - SUM(ISNULL(FB.Amount,0))[TrimesterBilledAmount]              
    FROM biz_FeeBill FB(NOLOCK)                        
    INNER JOIN biz_Installment IL(NOLOCK) ON FB.InstallmentID=IL.InstallmentID  AND ISNULL(FB.IsCancel,0)=1                 
    JOIN @result R  ON R.StudentID=FB.StudentID AND FB.SessionID=R.SessionID        
    WHERE (@SessionID IS NULL OR FB.SessionID=@SessionID)      
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,R.TrimesterBilledAmount                  
                  
    UPDATE @result SET TrimesterBilledAmount=M.TrimesterBilledAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   -----------------------******************************--------------------------------                    
   ----------------------------Fee Receipt --------------------------------------------                     
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount,PaidAmount ,Concession,OutstandingAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount - SUM(ISNULL(RD.DepositedAmount,0)) [BilledAmount], R.PaidAmount [PaidAmount],R.Concession,           
    R.OutstandingAmount - SUM(ISNULL(RD.DepositedAmount,0)) [OutstandingAmount]               
    FROM biz_FeeReceipt FR(NOLOCK)                        
    INNER JOIN biz_FeeReceiptDetails RD (NOLOCK) ON FR.FeeReceiptID=RD.FeeReceiptID                        
    LEFT JOIN biz_FeeBill FB (NOLOCK) ON FB.FeeBillID=RD.FeeBillID                      
    JOIN @result R  ON R.StudentID=FR.StudentID                    
    WHERE  (@SessionID IS NULL OR FR.SessionID <= @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.PaidAmount ,R.Concession,R.OutstandingAmount                  
              
                        
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     PaidAmount=M.PaidAmount,            
     Concession=M.Concession                  
    FROM @result R                        
    JOIN @misc M ON R.StudentID = M.StudentID                    
  ------------------------*****************************************----------------------    
  
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,PaidAmount)  
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.PaidAmount + SUM(ISNULL(RD.DepositedAmount,0)) [PaidAmount]        
    FROM biz_FeeReceipt FR(NOLOCK)                        
    INNER JOIN biz_FeeReceiptDetails RD (NOLOCK) ON FR.FeeReceiptID=RD.FeeReceiptID                        
    LEFT JOIN biz_FeeBill FB (NOLOCK) ON FB.FeeBillID=RD.FeeBillID                      
    JOIN @result R  ON R.StudentID=FR.StudentID                    
    WHERE  (@SessionID IS NULL OR FR.SessionID = @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName, R.PaidAmount     
                     
                  
    UPDATE @result SET PaidAmount=M.PaidAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   -----------------------******************************--------------------------------                         
   ---------------------------------Cancel Fee Receipt----------------------------------                        
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount,PaidAmount ,Concession,OutstandingAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount + SUM(ISNULL(RD.DepositedAmount,0))[BilledAmount], R.PaidAmount ,R.Concession,          
    R.OutstandingAmount + SUM(ISNULL(RD.DepositedAmount,0)) [OutstandingAmount]          
    FROM biz_FeeReceipt FR(NOLOCK)                        
    INNER JOIN biz_FeeReceiptDetails RD (NOLOCK) ON FR.FeeReceiptID=RD.FeeReceiptID  AND ISNULL(FR.IsCancel,0)=1                      
    LEFT JOIN biz_FeeBill FB (NOLOCK) ON FB.FeeBillID=RD.FeeBillID                        
    JOIN @result R  ON R.StudentID=FR.StudentID                    
    WHERE  (@SessionID IS NULL OR FR.SessionID <= @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.PaidAmount ,R.Concession,R.OutstandingAmount                
                         
                         
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     PaidAmount=M.PaidAmount,            
     Concession=M.Concession                   
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
                      
    ---------------------****************************************-----------------------             
    
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,PaidAmount)  
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.PaidAmount - SUM(ISNULL(RD.DepositedAmount,0)) [PaidAmount]        
    FROM biz_FeeReceipt FR(NOLOCK)                        
    INNER JOIN biz_FeeReceiptDetails RD (NOLOCK) ON FR.FeeReceiptID=RD.FeeReceiptID  AND ISNULL(FR.IsCancel,0)=1                        
    LEFT JOIN biz_FeeBill FB (NOLOCK) ON FB.FeeBillID=RD.FeeBillID                      
    JOIN @result R  ON R.StudentID=FR.StudentID                    
    WHERE  (@SessionID IS NULL OR FR.SessionID = @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName, R.PaidAmount     
                     
                  
    UPDATE @result SET PaidAmount=M.PaidAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   -----------------------******************************--------------------------------                 
   ------------------------- Fee Refund ----------------------------------------------                     
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount, AdjustedAmount ,Concession, OutstandingAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount - SUM(ISNULL(FR.Amount,0)) [BilledAmount], R.AdjustedAmount [AdjustedAmount] ,R.Concession,          
    R.OutstandingAmount - SUM(ISNULL(FR.Amount,0)) [OutstandingAmount]                   
    FROM biz_FeeRefund FR                     
    JOIN @result R  ON R.StudentID=FR.StudentID                     
    WHERE (@SessionID IS NULL OR FR.SessionID <= @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.AdjustedAmount ,R.Concession,R.OutstandingAmount                        
                        
                         
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     AdjustedAmount=M.AdjustedAmount,            
     Concession=M.Concession                      
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
                        
    -----*************************************************************************------   
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,AdjustedAmount)  
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.AdjustedAmount + SUM(ISNULL(FR.Amount,0))[AdjustedAmount]                  
    FROM biz_FeeRefund FR                     
    JOIN @result R  ON R.StudentID=FR.StudentID                     
    WHERE (@SessionID IS NULL OR FR.SessionID = @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,R.AdjustedAmount
                     
                  
    UPDATE @result SET AdjustedAmount=M.AdjustedAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   -----------------------******************************--------------------------------   
                    
     ------------------------- Cancel Fee Refund  --------------------------------------                     
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount,AdjustedAmount ,Concession,OutstandingAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount + SUM(ISNULL(FR.Amount,0)) [BilledAmount], R.AdjustedAmount [AdjustedAmount] ,R.Concession,          
    R.OutstandingAmount + SUM(ISNULL(FR.Amount,0)) [OutstandingAmount]             
    FROM biz_FeeRefund FR                     
    JOIN @result R  ON R.StudentID=FR.StudentID AND ISNULL(FR.IsCancel,0)=1                    
    WHERE  (@SessionID IS NULL OR FR.SessionID <= @SessionID)                     
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.AdjustedAmount ,R.Concession,R.OutstandingAmount                         
                        
                     
                         
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     AdjustedAmount=M.AdjustedAmount,            
     Concession=M.Concession                    
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
                        
    -----*************************************************************************------                    
  
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,AdjustedAmount)  
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.AdjustedAmount - SUM(ISNULL(FR.Amount,0))[AdjustedAmount]                  
    FROM biz_FeeRefund FR                     
    JOIN @result R  ON R.StudentID=FR.StudentID  AND ISNULL(FR.IsCancel,0)=1                    
    WHERE (@SessionID IS NULL OR FR.SessionID = @SessionID)                    
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,R.AdjustedAmount
                     
                  
    UPDATE @result SET AdjustedAmount=M.AdjustedAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   -----------------------******************************--------------------------------   
                      
                        
     ------------------------- Refund Payment  -----------------------------------------                     
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount,PaymentAmount ,Concession,OutstandingAmount)                    
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount + SUM(ISNULL(PD.PaymentAmount,0)) [BilledAmount], R.PaymentAmount  [PaymentAmount] ,R.Concession,          
    R.OutstandingAmount + SUM(ISNULL(PD.PaymentAmount,0)) [OutstandingAmount]                  
    FROM biz_FeePayment FP(NOLOCK)                         
    INNER JOIN biz_FeePaymentDetails PD (NOLOCK) ON FP.FeePaymentID=PD.FeePaymentID                    
    INNER JOIN @result R  ON R.StudentID=FP.StudentID                     
    WHERE  (@SessionID IS NULL OR FP.SessionID<=@SessionID)                        
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.PaymentAmount ,R.Concession,R.OutstandingAmount                        
                      
                     
                        
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     PaymentAmount=M.PaymentAmount,            
     Concession=M.Concession                     
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
                        
    -----*************************************************************************------            
          
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,PaymentAmount)  
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.PaymentAmount + SUM(ISNULL(PD.PaymentAmount,0))  [PaymentAmount]                
    FROM biz_FeePayment FP(NOLOCK)                         
    INNER JOIN biz_FeePaymentDetails PD (NOLOCK) ON FP.FeePaymentID=PD.FeePaymentID                    
    INNER JOIN @result R  ON R.StudentID=FP.StudentID                     
    WHERE  (@SessionID IS NULL OR FP.SessionID=@SessionID)                        
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,R.PaymentAmount
                     
                  
    UPDATE @result SET PaymentAmount=M.PaymentAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
   -----------------------******************************--------------------------------                  
                        
     ------------------------- Cancel Refund Payment  ----------------------------------                     
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,
    BilledAmount, PaymentAmount ,Concession,OutstandingAmount)         
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount - SUM(ISNULL(PD.PaymentAmount,0)) [BilledAmount], R.PaymentAmount[PaymentAmount] ,R.Concession,          
    R.OutstandingAmount - SUM(ISNULL(PD.PaymentAmount,0)) [OutstandingAmount]                   
    FROM biz_FeePayment FP(NOLOCK)                         
    INNER JOIN biz_FeePaymentDetails PD (NOLOCK) ON ISNULL(FP.IsCancel,0)=1 AND FP.FeePaymentID=PD.FeePaymentID                    
    INNER JOIN @result R  ON R.StudentID=FP.StudentID              
    WHERE   (@SessionID IS NULL OR FP.SessionID<=@SessionID)               
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.BilledAmount, R.PaymentAmount ,R.Concession,R.OutstandingAmount 
                      
                        
    UPDATE @result SET OutstandingAmount=M.OutstandingAmount,            
     BilledAmount=M.BilledAmount,            
     PaymentAmount=M.PaymentAmount,            
     Concession=M.Concession                    
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
                        
    -----*************************************************************************------      
    
            
    DELETE FROM @misc                    
                        
    INSERT INTO @misc (ShiftID,CourseID,SpecializationID,Batch,StudentID,AdmissionNumber,StudentName,PaymentAmount)  
    SELECT R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,
    R.PaymentAmount - SUM(ISNULL(PD.PaymentAmount,0))  [PaymentAmount]                
    FROM biz_FeePayment FP(NOLOCK)                         
    INNER JOIN biz_FeePaymentDetails PD (NOLOCK) ON FP.FeePaymentID=PD.FeePaymentID  AND ISNULL(FP.IsCancel,0)=1                  
    INNER JOIN @result R  ON R.StudentID=FP.StudentID                     
    WHERE  (@SessionID IS NULL OR FP.SessionID=@SessionID)                        
    GROUP BY R.ShiftID,R.CourseID,R.SpecializationID,R.Batch,R.StudentID,R.AdmissionNumber,R.StudentName,R.PaymentAmount
                     
                  
    UPDATE @result SET PaymentAmount=M.PaymentAmount        
    FROM @result R                    
    JOIN @misc M ON R.StudentID = M.StudentID                    
                            
                        
                               
 SELECT * FROM @result 
   
   
   
                     
 SET NOCOUNT OFF

What I have tried:

above i write query and get record 1011 only but it take 1.51 min and throw exception

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

so how to Resolve Please help

Posted 2-Mar-20 19:39pm

Member 12183079

Updated 2-Mar-20 20:57pm

Maciej Los

v3

Add a Solution

2 solutions

Solution 1

First of all, no one is able to indentify cause of above error in such of long query.

Please, read this: c# - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated - Stack Overflow[^]
There you'll find detailed information about reasons of "time out exception" error.

Posted 2-Mar-20 20:02pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2020-03-02T20:57:00

Start with copying the content of the procedure to SSMS.
Remove the alter procedure statement and comment out everything else.
Then set all Parameters manually to the test values.
Then you remove the comments for the first statement, and run it.
If it works fine you comment the first statement and uncomment the second statement. And run again.

Keep doing this until you have found which statements are causing you trouble.
Try to analyze why that statement is slow, and eventually you can ask us again.