July 20, 2008 at 10:51 pm
Hello guys,
my question is: Is there any performance defects if I use Try...catch blocks in my stored procedure?
Regards,
Elton
July 20, 2008 at 11:32 pm
July 20, 2008 at 11:40 pm
before using it just go through the Try-Catch Concepts in Sql Server 2005, because it has some limitation, I personally prefer to use custom error handling method in SQL Server 2005 too.
Cheers!
Sandy.
--
July 20, 2008 at 11:42 pm
I guess TRY CATCH block improves the error handling in store procedure, it doesn't have any performance issues with it.
July 21, 2008 at 1:03 am
Just for error handeing, no issue of performance. Also, I think, using try / catch isd fast in case of execution, as it directly goes to error - handeler on error. In another casem, the next lines are executed or tried to be executed (in some cases.)
Atif Sheikh
July 21, 2008 at 6:30 am
TRY/CATCH itself has no performance implications. What you do in the CATCH statement could have serious performance implications.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2008 at 10:53 am
Just as a test, I ran:
create table #Runtime (
Version int,
Runtime int)
go
declare @Start datetime, @X int
select @start = getdate()
select @x = col2
from dbo.sometable
insert into #runtime (version, runtime)
select 1, datediff(ms, @start, getdate())
go 100
begin try
declare @Start datetime, @X int
select @start = getdate()
select @x = col2
from dbo.sometable
end try
begin catch
end catch
insert into #runtime (version, runtime)
select 2, datediff(ms, @start, getdate())
go 100
select version,
avg(runtime) as [avg],
max(runtime) as [max],
min(runtime) as [min]
from #runtime
group by version
Average runtime was identical (289 ms), max was nearly so (330 vs 313, try/catch had lower number), min was identical (280). The slight variation in max doesn't mean anything.
So, Try/Catch doesn't impact performance, unless your Catch does something intensive.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2008 at 6:09 pm
Odd SSCrazy:
When I do the test with select:
declare @Runtime table (
Version int,
Runtime int)
declare @Start datetime, @X int
select @start = getdate()
select top 100 * from b_order
insert into @Runtime (version, runtime)
select 1, datediff(ms, @start, getdate())
begin try
select @start = getdate()
select top 100 * from b_order
insert into @Runtime (version, runtime)
select 2, datediff(ms, @start, getdate())
end try
begin catch
the result I got is
version runtime
1 0
2 30
(average result)
When I use "select top 1000 * from b_order"
the result I got is
version runtime
1 390
2 420
When I use "select top 10000 * from b_order"
the result I got is
version runtime
1 700
2 717
Seems there's some overhead for try..catach, is it?
Elton
July 22, 2008 at 11:32 am
Your test has more variables in it than just Try/Catch. For example, the time to return the result-set to the connection and paint it to the screen. (That's why I did my select the way I did.)
Try the test I did, using whatever table you like. Mine's a 1-million-row table of random data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 5:36 pm
Yes your are right GSquared,
I did sth stupid,
The test should be:
declare @Runtime table (
Version int,
Runtime int)
declare @Start datetime, @X int
select @start = getdate()
select top 1000 * from b_order
insert into @Runtime (version, runtime)
select 1, datediff(ms, @start, getdate())
begin try
select @start = getdate()
select top 1000 * from b_order
insert into @Runtime (version, runtime)
select 2, datediff(ms, @start, getdate())
end try
begin catch
end catch
Thanks
July 27, 2008 at 1:37 am
It wont affect the performance of your query.
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
July 28, 2008 at 8:23 am
Executing within a TRY/CATCH block won't affect performance. However, the setup and tear down of the construct does have its overhead. Run the following:
Use TempDB;
Declare @i Int,@st DateTime,@a Int;
Select @i=100000,@a=0,@st=GetDate()
While @i>0 Begin
Set @i=@i-1
Set @a=@a+1
End
Print DateDiff(ms,@st,GetDate())
Begin Try
Select @i=100000,@a=0,@st=GetDate()
While @i>0 Begin
Set @i=@i-1
Set @a=@a+1
End
Print DateDiff(ms,@st,GetDate())
End Try
Begin Catch
End Catch
Select @i=100000,@a=0,@st=GetDate()
While @i>0 Begin
Set @i=@i-1
Begin Try
Set @a=@a+1
End Try
Begin Catch
End Catch
End
Print DateDiff(ms,@st,GetDate())
July 31, 2008 at 6:57 am
Abnormal exit (like a throw or return or error exception) of a try block has a big performance hit. Normal entry and exit of a try block has no significant impact. So you should not put return in the middle of a try block, even though putting return is normal practice elsewhere. Throw and exceptions are (/should be) rare so you need not worry about their performance hit. Now I don't know much about T-SQL to know if it has a return statement, I am talking about other languages' here.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply