x

how to increase performance of store procedure

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'

////////////////////////////////////////////////////

more ▼

asked Apr 20, 2010 at 05:48 AM in Default

avatar image

Pankaj
1 1 1 1

It is a lot easier to help people here when the question is concise and easily read. The question creation dialog has several facilities to help you layout your question. I have tried to apply some better formatting but I dont want to change it too much incase I change its meaning. Its also going to be tricky for people to comment when they dont have the DDL statements for the table your are referencing or any information about the amount of data etc. Anyway, I hope you get an answer that means your query runs in 5-10sec as you request.

Apr 20, 2010 at 06:08 AM Fatherjack ♦♦

@Fatherjack... Patience of a saint!

Apr 20, 2010 at 06:11 AM sp_lock

I'm sorry. I'm trying to help, but this is making my head hurt. Flat out, as Matt says below, you're cyclying through all your rows one at a time. That is absolutely the worst possible way to go about anything in the database. Second, since we're not in your head, it's hard to know what's going on with this. Documentation, an execution plan or two and some idea of what's happening would be necessary for us to really help.

Apr 20, 2010 at 08:23 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I can't really tell what you're trying to achieve from a stored procedure given there is no description of it and no DDL, as Fatherjack pointed out.

However, I can tell you that it is probably slow because you are doing data manipulation in a loop, rather than using a set based update.

Out of interest - how long does this bit take to run?

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)
more ▼

answered Apr 20, 2010 at 06:37 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

Thanks friends for u r quick reply. I am using the above store procedure for calculation monthly performance of the employees in a firm(which calculate their monthly presents, absent,weekoff,latecome,earlygo etc..)

Yes Mr.Matt i hav used loop here because it is caculating the above information for the whole employees of a firm.

Apr 20, 2010 at 06:53 AM Pankaj

Let me know how long the insert into #muster bit takes - because if it is quick, then it is very likely that it is your loop that is slowing things down. You need to use set based statements that work on all the data at once, rather than using a row-by-agonising-row method.

Apr 20, 2010 at 07:11 AM Matt Whitfield ♦♦

Hi matt , the insert into #muster bit takes very less time. can u explain u r ideas with some more discriptive way.what should i hav to do instead of the loop??

Apr 20, 2010 at 07:41 AM Pankaj

Try reading this: http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/

Basically you need to achieve your requirements without using while loops etc

Apr 20, 2010 at 08:32 AM Matt Whitfield ♦♦

I agree with Matt, this type of code is no better than Cursors, and SQL server has never been fast with cursors. You may get away with cursors in Oracle but not in SQL server.

Apr 20, 2010 at 09:45 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

I don't know what your data structure looks like, but if you want real performance, then you need to get away from this type of code:

from department 
where compcode=''+@compcode+'' 
and loccode=''+@loccode+'' 
and deptcode=''+@deptcode+''

If compcode is a varchar and @compcode is a varchar, then you only need to do this:

compcode = @compcode

Otherwise, you're going to be getting calculations and best guesses by the optimizer instead of letting it use the parameters to guess at the best way to retrieve the data. Also, doing this with anything other than strings means you're forcing a data conversion which will prevent the optimizer from using indexes to retrieve your data.

Editing to add in this after a bit more digging:

and [date] between '''+convert(char(12),@fromdate,6)+''' and '''+convert(char(12),

And there is one instance where you're converting dates to strings to compare to dates where you will lose performance and get table or index scans where you should get seeks (assuming you have good indexes in place) because of data type conversions.

more ▼

answered Apr 20, 2010 at 08:30 AM

avatar image

Grant Fritchey ♦♦
137k 20 42 81

  • But one feels that the haystack is full of needles... :/

Apr 20, 2010 at 08:33 AM Matt Whitfield ♦♦

@Matt - that would be a needlestack then.

Apr 20, 2010 at 09:16 AM Fatherjack ♦♦

HAY - that's funny!

Apr 20, 2010 at 09:51 AM Blackhawk-17

That is a good one.

Apr 20, 2010 at 10:23 AM Grant Fritchey ♦♦

@Jack - LOL - 'trying to find some hay in a needlestack' does have a nice, and somewhat more sinister, ring to it :)

Apr 20, 2010 at 10:27 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2016
x454
x288

asked: Apr 20, 2010 at 05:48 AM

Seen: 3741 times

Last Updated: Apr 20, 2010 at 08:34 AM

Copyright 2016 Redgate Software. Privacy Policy