Riddhi Valecha

Riddhi Valecha

  • 628
  • 3.2k
  • 332.7k

Fetching records is taking more time while executing Stored PRocedure

Dec 3 2021 5:08 PM

Dear Team,

My requirement is - I need to fetch yearly records of students attendance from SQL Server Stored Procedure.

For that, I have made the Stored Procedure. But, the SP is taking lot of time while executing.

I am sharing my Stored PRocedure -

alter procedure [dbo].[SP_Student_Attendance]                        
(
@FromDate Date  , @ToDate Date  , @Class int
)
as
Begin
 set nocount on
 declare @Date Date
 declare @Day int
 declare @Dayloop int
 declare @strString nvarchar(max)
 declare @strString1 nvarchar(max)
 declare @Yr int
 declare @YrPlus int
 declare @FD date
 set @Date = @FromDate
 set @Day=day(@Date)
 set @FD = @FromDate
create table #temp_Student_Attendance(AM_ID bigint, Class varchar(100), Division varchar(100), Roll_No varchar(20),
       [Name] varchar(255), CM_ID bigint,Gender char(50),Category char(250),Caste char(250),SubCaste char(250),Status varchar(250),
    Admission_Category varchar(100))
   begin
   set @strString='insert into #temp_Student_Attendance(AM_ID, Class, Division,Roll_No,[Name],CM_ID,Gender,Category,Caste,SubCaste,Status,Admission_Category)
       select distinct a.AM_ID, b.Class, b.Division, b.Roll_No,  (case when a.Candidate_FName is not null then a.Candidate_FName else '''' end +
       case when isnull(a.Candidate_MName,'''')<>'''' then '' ''+a.Candidate_MName else '''' end +                   
       case when isnull(a.Candidate_LName,'''')<>'''' then '' ''+a.Candidate_LName else '''' end) [Name],b.SrNo,
       Gender,
       c.description as Category,
       a.Caste,a.SubCaste,
       e.description , a.Admission_Category
       from dbo.Admission_Master a, dbo.Class_Master b,
       TBLMainMaster c ,
       TBLMainMaster e ,  DAV_Student_Attendance f
       where a.AM_ID=b.AM_ID and a.active=1 and b.active=1
    and format( cast(f.Date as date),''dd-MMM-yyyy'')  between  ''' + format( cast(@FromDate as date),'dd-MMM-yyyy') + '''  and    ''' + format( cast(@ToDate as date),'dd-MMM-yyyy') + '''
  and f.Active = 1
  and f.CM_ID in  (select srno from DAV_Class_Master d where d.Active = 1 and a.AM_ID = d.AM_ID)
       and a.category=c.sub_code and c.Main_Code=3
       and e.sub_code=a.status and e.main_code=9
       and b.Division is not null and a.Status=4 and b.Period='''+cast(@Period  as varchar)+''' '
    set @strString1='insert into #temp_Student_Attendance(AM_ID, Class, Division,Roll_No,[Name],CM_ID,Gender,Category,Caste,SubCaste,Status,Admission_Category)
       Select distinct a.AM_ID, b.Class, b.Division, b.Roll_No,  (case when a.Candidate_FName is not null then a.Candidate_FName else '''' end +
    Case when isnull(a.Candidate_MName,'''')<>'''' then '' ''+a.Candidate_MName else '''' end +
    Case when isnull(a.Candidate_LName,'''')<>'''' then '' ''+a.Candidate_LName else '''' end)[Name],b.SrNo,
    Gender,a.Caste,a.SubCaste,                  
       --c.description as Category,case when a.Category=21 then Category_Others else '''' end Category_Others,                  
       c.description as Category,                  
       e.description  , a.Admission_Category                 
    from dbo.DAV_Admission_Master a inner join DAV_Class_Master b  on a.AM_ID=b.AM_ID and a.active=1 and b.active=1                  
    inner join TBLMainMaster c on a.category=c.sub_code And c.Main_Code=3                  
    inner join dav_student_tc_details d on a.am_id=d.AM_ID                  
       inner join TBLMainMaster e on e.sub_code=a.status and e.main_code=9  where                   
    format( cast(d.[created_dt] as date),''dd-MMM-yyyy'') between  ''' + format( cast(@FromDate as date),'dd-MMM-yyyy') + '''  and    ''' + format( cast(@ToDate as date),'dd-MMM-yyyy') + '''                           
    and b.Division is not null and a.Status=4 and b.Period='''+cast(@Period as varchar)+''' '                     
   print @strString;                      
   print @strString1;                    
                          
 if @Class is not null                          
 Begin                          
     set @strString=@strString+' and b.Class='+cast(@Class as varchar)                  
     set @strString1=@strString1+' and b.Class='+cast(@Class as varchar)                  
 End                                   
 exec sp_executesql @strString                  
       exec sp_executesql @strString1                       
   print @strString;                    
        print @strString1;                  
 set @Dayloop=1                          
                       
 while @Date<=@ToDate                    
 Begin                          
               
  set @strString='alter table #temp_Student_Attendance add ['+convert(varchar,@Date,106)+'] varchar(50)'                         
  print @strString;                    
  exec sp_executesql @strString                          
  set @strString='update #temp_Student_Attendance set ['+convert(varchar,@Date,106)+']=                          
  (select case when b.Attendance_Status=1 then ''P'' when b.Attendance_Status=0 then ''A'' else '''' end                           
  from #temp_Student_Attendance a, DAV_Student_Attendance b                          
  where a.CM_ID=b.CM_ID and b.active=1
  and convert(varchar,format( cast(b.Date as date),''dd-MMM-yyyy''))  = ''' + convert(varchar, format( cast(@FromDate as date),'dd-MMM-yyyy')) + ''')'  
  print @strString ;                       
  exec sp_executesql @strString                          
  set @Dayloop=@Dayloop+1                       
  set @Date = DATEADD(day,1,@Date)                     
  set @FromDate = DATEADD(day,1,@FromDate)    
 End                         
set @Date = @FD                     
 update #temp_Student_Attendance set Class=b.Description                          
 from #temp_Student_Attendance a, TBLMainMaster b                          
 where a.Class=b.Sub_Code and b.Main_Code=1 and b.active=1                           
 update #temp_Student_Attendance set Division=b.Description                          
 from #temp_Student_Attendance a, TBLMainMaster b                          
 where a.Division=b.Sub_Code and b.Main_Code=13 and b.active=1                         
 alter table #temp_Student_Attendance add Current_Total int, Previous_Total int, Grand_Total int                          
 set @strString='update #temp_Student_Attendance set Current_Total=b.Total                           
 from #temp_Student_Attendance a,                           
 (select CM_ID, sum(case when b.Attendance_Status=1 then 1 else 0 end) Total from DAV_Student_Attendance b                           
 where b.active='+ @Act +' and                         
 format( cast(b.Date as date),''dd-MMM-yyyy'')  between '''+format( cast(@FD  as date),'dd-MMM-yyyy')+''' and '''+format( cast(@ToDate as date),'dd-MMM-yyyy')+'''                      
 group by CM_ID) b                          
 where a.CM_ID=b.CM_ID'                    
 print @strString                    
 exec sp_executesql @strString                          
 set @strString='update #temp_Student_Attendance set Previous_Total=isnull(b.Total,0)                          
 from #temp_Student_Attendance a,                           
 (select CM_ID, sum(case when b.Attendance_Status=1 then 1 else 0 end) Total from DAV_Student_Attendance b                           
 where b.active='+ @Act +'                    
 and format( cast(b.Date as date),''dd-MMM-yyyy'')   between ''' + format( cast(@FD  as date),'dd-MMM-yyyy') + '''  and    ''' + format( cast(@ToDate as date),'dd-MMM-yyyy') + '''                    
 group by CM_ID) b where a.CM_ID=b.CM_ID'                        
 print @strString;                    
 exec sp_executesql @strString                          
 set @strString='update #temp_Student_Attendance set Grand_Total=isnull(Current_Total,0)+isnull(Previous_Total,0)'                          
 exec sp_executesql @strString                          
 set @strString='select AM_ID,Class,Division,Roll_No,[Name],Gender,Caste,SubCaste,Status,      
 (Select Description from TBLMainMaster where Main_Code=28 and Sub_Code = Admission_Category) "Admission_Category" '                        
 set @Dayloop=1                          
 set @Date = @FD                     
 while @Date<=@ToDate                    
 Begin                          
   set @strString=@strString+',['+convert(varchar,@Date,106)+']'               
  set @Dayloop=@Dayloop+1                          
  set @Date = DATEADD(day,1,@Date)                     
 End
 set @strString=@strString+', Current_Total, Previous_Total, Grand_Total from #temp_Student_Attendance order by Class, Division, [Name]'
 exec sp_executesql @strString
End 

 


Answers (7)