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

Pankaj gravatar 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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1 - 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:

x1951
x415
x252

asked: Apr 20, 2010 at 05:48 AM

Seen: 3476 times

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