about the performance of stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

about the performance of stored procedure

Author

Topic

billdng
Starting Member

17 Posts

Posted - 2008-11-26 : 01:33:46

dear all ,
Now ,the efficiency of the SP is very low. so i want your advice about how can I optimize the performance of sql sentence . Thanks a lot.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-26 : 01:39:28

Run the SP and see Execution Plan or Profiler.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

billdng
Starting Member

17 Posts

Posted - 2008-11-26 : 01:46:07

dear karthickbabu,
I learn SQL server 2005 about three month ,and not very familiar with it .How I can see the Execution Plan ? and what is 'profiler' mean ? Thanks .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 01:59:04

quote:
Originally posted by billdng

dear karthickbabu,
I learn SQL server 2005 about three month ,and not very familiar with it .How I can see the Execution Plan ? and what is 'profiler' mean ? Thanks .

you've option available on sql mgmnt studio to turn on actual execution plan in top menu.
you can find profiler under start-> programs-> microsoft sql server 2005-> performancetools

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-26 : 01:59:59

Execuation Plan is the time calculated for executing our query.

If your are using Management Studio, SELECT your Query and Click
Menu Query->Display Estimated Executed Plan.

Profiler is a tracer to calculated line by line execution, it has some process.

First you learn about this.

Can you post that Content of your SP.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

billdng
Starting Member

17 Posts

Posted - 2008-11-26 : 02:10:46

Thanks a lot .

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-26 : 02:37:08

Welcome

====================================================
you realize you've made a mistake, take immediate steps to correct it.

billdng
Starting Member

17 Posts

Posted - 2008-11-26 : 02:39:43

The function of my SP is Calculate the Employees's Attendance Status . such as the unusual status(Late Time, Early Time,absent time and other),Over Time, Holiday,work Days and so on .
the parameter of the sp is BeginDate,EndDate,Deptment .

i have two choice to complete it :
the first one is :
declare @AttendDay datetime
set @attendDay =@begindate
while(@Attendday <=@EndDate)
begin
1.to fetch the value of CardTime from the table(originalityCard)
when AttendDate =@AttendDay
2.Calculate the unusual attendance status
when AttendDate =@AttendDay
3.Calculate the Over time
when AttendDate =@AttendDay
4.Calculate the Holiday
when AttendDate =@AttendDay
5.Calculate the work Days
when AttendDate =@AttendDay

select @AttendDay = dateadd(day,1,@AttendDay)

End

the second one is :
declare @AttendDay datetime
set @attendDay =@begindate
while(@Attendday <=@EndDate)
begin

1.to fetch the value of CardTime from the table(originalityCard)
when AttendDate =@AttendDay
2.Calculate the unusual attendance status
when AttendDate =@AttendDay
select @AttendDay = dateadd(day,1,@AttendDay)
End

3.Calculate the Over time
when AttendDate between @BeginDate and @EndDate
4.Calculate the Holiday
when AttendDate between @BeginDate and @EndDate
5.Calculate the work Days
when AttendDate between @BeginDate and @EndDate

from above sentences ,you can find which diference between the two choice .
My question is:
which one is fast when the Employees to be calculate are more ?
which one is fast when the Employees to be calculate are less?

P.S:because our company is in Manufacture sector . and have more than ten thousands employees, so the time cost by Calculate their attendance is very long . it's waste time for our HR operator. they have to spent much time to wait the result of the calculation.
so can anybody give me any advice to design the structure of the Attendance Calculate SP?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 03:42:56

why are using a while loop? cant you go for set based operation?

billdng
Starting Member

17 Posts

Posted - 2008-11-26 : 04:58:45

quote:
Originally posted by visakh16

why are using a while loop? cant you go for set based operation?

I can execute the SP with the parameter AttendDate Day after day ,because of this ,i use the while loop. is it matter ? thanks for your reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 05:22:59

why do you want to execute sp in a loop? whats does sp do when you pass a day value?
cant you rewrite sp in such a way that it does operation set based grouped on day value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources