Hello everyone !!!
My store procedure take 40-50 sec execution time can anyone refere me some solution to reduce it to 5-10 sec??
////////////////////////////////////////////////////
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--exec rpt_yearlyperformance '20000','m01','61752','01-jan-2010','31-dec-2010'
--exec rpt_yearlyperformance '20000','m01','21022','01-jan-2010','31-dec-2010'
--truncate table tempperformance
--select * from tempperformance
ALTER proc [dbo].[RPT_YEARLYPERFORMANCE](
@compcode varchar(10),
@loccode varchar(10),
@empcode varchar(10),
@fromdate datetime,
@todate datetime
)
as
set nocount on
Begin try
declare @musteryear varchar(20),@rptmonth varchar(3),@query varchar(max),@monthval varchar(15),@monthvalue varchar(15),@year varchar(5)
set @musteryear=ltrim(rtrim('muster_'+cast(year(@fromdate) as char)))
set @rptmonth=month(@fromdate)
set @year =year(@fromdate)
--PRINT @musteryear print @rptmonth
declare @empname varchar(50),@deptcode varchar(15),@gradecode varchar(5),@deptname varchar(50),@catcode varchar(10),@catname varchar(50),@tempempcount int,@leftdate datetime
declare @presentcount float,@absentcount float,@leavecount float,@weekoffcount float,@holidaycount float,@lwplevcount float
declare @halfprecount float,@halfabscount float,@halflevcount float,@halflwplevcount float
declare @mdate datetime,@mlate varchar(5),@mearly varchar(5),@mleave varchar(5),@mhalfday varchar(5),@mstatus varchar(4),@mmonthval varchar(12)
declare @latecount float,@earlycount float,@latecounthrs float,@total float
declare @pretotal float,@abstotal float,@levtotal float,@persentage float,@paid varchar(1)
set @presentcount=0 set @halfprecount=0 set @absentcount=0 set @halfabscount=0 set @leavecount=0 set @halflevcount=0 set @weekoffcount=0 set @holidaycount=0
set @pretotal=0 set @abstotal=0 set @levtotal=0 set @persentage=0 set @lwplevcount=0 set @halflwplevcount=0 set @total=0 set @tempempcount=0 set @paid=0
set @latecount=0 set @earlycount=0 set @latecounthrs=0
select @leftdate=leftdate,@empname
=[name],@gradecode=gradecode,@deptcode=deptcode,@catcode=catcode
from employee_job
where empcode=''+@empcode+''
and compcode=''+@compcode+''
and loccode=''+@loccode+''
--PRINT @leftdate PRINT @empname PRINT @gradecode PRINT @deptcode PRINT 'OK1 SO ON'
select @deptname=departmentname
from department
where compcode=''+@compcode+''
and loccode=''+@loccode+''
and deptcode=''+@deptcode+''
--PRINT @deptname PRINT 'OK2 SO ON'
select @catname=categoryname
from category
where compcode=''+@compcode+''
and loccode=''+@loccode+''
and catcode=''+@catcode+''
--PRINT 'cat'print @catname print 'catend'
delete from tempperformance
where empcode=''+@empcode+''
and [year]=''+@year+''
and compcode=''+@compcode+''
and loccode=''+@loccode+''
if(@leftdate<>'' or @leftdate is not null)
begin
set @todate=@leftdate
end
--create hash table
create table #muster (RowId int primary key identity(1,1),Date datetime,Late Varchar(6),Early Varchar(6),Leave Varchar(5),HalfDay varchar(4),Status Varchar(3),monthval Varchar(15))
set @query ='insert into #muster(Date,Late,Early,Leave,HalfDay,Status,monthval)
(select date,late,early,leave,halfday,status,(datename(MM,date)) as monthval
from ['+@musteryear+']
where empcode='''+@empcode+'''
and [date] between '''+convert(char(12),@fromdate,6)+''' and '''+convert(char(12),@todate,6)+'''
and compcode='''+@compcode+'''
and loccode='''+@loccode+''')
order by date'
--PRINT @query
exec(@query)
select * from #muster
declare @MinRowId int,@MaxRowId int,@CurrRowId int
select @MinRowId=min(rowid),@MaxRowId=max(rowid) from #muster
set @CurrRowId=@MinRowId
--PRINT @CurrRowId PRINT @MaxRowId
while @CurrRowId<=@MaxRowId
Begin
--PRINT '111'
select @mdate=date,@mlate=late,@mearly=early,@mleave=leave,@mhalfday=halfday,@mstatus=status,@mmonthval=monthval from #muster where rowid=@CurrRowId order by date
--PRINT @mmonthval PRINT @monthvalue
if (@mmonthval<>@monthvalue)
begin
--PRINT '444'
set @presentcount=0 set @halfprecount=0 set @absentcount=0 set @halfabscount=0 set @leavecount=0 set @halflevcount=0
set @weekoffcount=0 set @holidaycount=0 set @earlycount=0 set @pretotal=0 set @abstotal=0 set @levtotal=0
set @lwplevcount=0 set @halflwplevcount=0 set @latecount=0 set @latecounthrs=0 set @persentage=0
end
set @monthvalue=@mmonthval
--PRINT @monthvalue
if(@monthvalue=@mmonthval)
begin
--PRINT '222'
--calculation late and latehrs
if (@mlate<>'')
begin
set @latecount=@latecount+1
set @latecounthrs=@latecounthrs+dbo.mins(@mlate)
end
--calculating early count
if (@mearly<>'')
begin
set @earlycount=@earlycount+1
end
--calculating present count
if ((@mstatus='PP' or @mstatus='PP*') and @mleave='')
begin
set @presentcount=@presentcount+1
end
if (@mstatus like '%P%' and @mstatus<>'PP' and @mstatus<>'PP*' )
begin
set @presentcount=@presentcount+0.5
end
--calculatiing absent count
if (@mstatus='AA')
begin
set @absentcount=@absentcount+1
end
if (@mstatus like '%A%' and @mstatus<>'AA')
begin
set @absentcount=@absentcount+0.5
end
--calculating weekly off
if (@mleave='WO')
begin
set @weekoffcount=@weekoffcount+1
end
--calculating holiday
if(@mleave='HL')
begin
set @holidaycount=@holidaycount+1
end
--calculating leave count
select @paid=paid from leave where compcode=''+@compcode+'' and loccode=''+@loccode+'' and leavecode=''+@mleave+''
if (@paid='1' and @mleave<>'' and @mleave not in('WO','HL'))
begin
set @leavecount=@leavecount + 1
end
if (@paid='1' and @mleave='' and @mhalfday<>'' and @mhalfday<>'HF')
begin
set @leavecount=@leavecount+0.5
end
if(@paid='0' and @mleave<>'' and @mleave not in('WO','HL'))
begin
set @lwplevcount=@lwplevcount +1
end
if(@paid='0' and @mleave='' and @mhalfday<>'' and @mhalfday<>'HF')
begin
set @lwplevcount=@lwplevcount+0.5
end
set @total=@presentcount+@absentcount+@weekoffcount+@holidaycount+@leavecount+@lwplevcount
--PRINT @total
end
set @CurrRowId=@CurrRowId+1
--PRINT @CurrRowId
select @tempempcount=count(empcode) from tempperformance where empcode=''+@empcode+'' and [month]=''+@monthvalue+'' and compcode=''+@compcode+'' and loccode=''+@loccode+''
--PRINT @tempempcount
if (@tempempcount=0)
begin
--PRINT '333'
insert into tempperformance(empcode,empname,catcode,GradeCode,catname,deptcode,deptname,compcode,loccode,[month],[year],present,weekoff,holiday,leave,absent,total,late,early,totallatemin,lwp)
values(@empcode,@empname,@catcode,@gradecode,@catname,@deptcode,@deptname,@compcode,@loccode,@mmonthval,@year,cast(@presentcount as char),cast(@weekoffcount as char),cast(@holidaycount as char),cast(@leavecount as char),cast(@absentcount as char),cast(@total as char),cast(@latecount as char),cast(@earlycount as char),cast(@latecounthrs as char),cast(@lwplevcount as char))
end
if(@tempempcount>0)
begin
--PRINT '444'
update tempperformance set present=''+@presentcount+'',weekoff=''+@weekoffcount+'',holiday=''+@holidaycount+'',leave=''+@leavecount+'',absent=''+@absentcount+'',total=''+@total+'',late=''+@latecount+'',early=''+@earlycount+'',totallatemin=''+@latecounthrs+'' where compcode=''+@compcode+'' and loccode=''+@loccode+'' and empcode=''+@empcode+'' and [month]=''+@mmonthval+''
end
End
End Try
Begin Catch
--select ERROR_Message() as Message,ERROR_NUMBER() as Number,ERROR_SEVERITY() as Serverity,ERROR_LINE() as LineNumber,ERROR_PROCEDURE() as Proc_Name,ERROR_STATE() as State
End Catch
--print @empcode print @empname print @deptcode print @deptname print @compcode print @loccode print @mmonthval print @presentcount print @weekoffcount
--print @holidaycount print @leavecount print @absentcount print @total print @latecount print @earlycount print @latecounthrs print @lwplevcount
--exec rpt_yearlyperformance '00001','00001','00454545','01-jan-2009','31-dec-2009'
--select * from tempperformance
--truncate table tempperformance
--select * from muster_2009 where empcode='12345678'
////////////////////////////////////////////////////
