(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&lt;=@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&lt;&gt;@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&lt;&gt;'')
begin
set @latecount=@latecount+1
set @latecounthrs=@latecounthrs+dbo.mins(@mlate)
end
--calculating early count
if (@mearly&lt;&gt;'')
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&lt;&gt;'PP' and @mstatus&lt;&gt;'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&lt;&gt;'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&lt;&gt;'' and @mleave not in('WO','HL'))
begin
set @leavecount=@leavecount + 1
end
if (@paid='1' and @mleave='' and @mhalfday&lt;&gt;'' and @mhalfday&lt;&gt;'HF')
begin
set @leavecount=@leavecount+0.5
end
if(@paid='0' and @mleave&lt;&gt;'' and @mleave not in('WO','HL'))
begin
set @lwplevcount=@lwplevcount +1
end
if(@paid='0' and @mleave='' and @mhalfday&lt;&gt;'' and @mhalfday&lt;&gt;'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&gt;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

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.

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.

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)

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.

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.

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.