Which is more expensive?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Which is more expensive? Trapping an Exception to prevent duplicate entries
in a database, or using IF EXISTS in T-SQL?

Also, how would I go about testing which is more expensive myself?

Thanks
 
Which is more expensive? Trapping an Exception to prevent duplicate entries
in a database, or using IF EXISTS in T-SQL?

Also, how would I go about testing which is more expensive myself?

Thanks
Click to expand...

For a single insert in your stored procedure when IF EXISTS is true
and/or an index violation is thrown, I don't know.

But if you are talking across the breadth of many rows, each with
their own invocation of your stored procedure - and there were few to
no duplicates then it would definitely be slower to test before
inserting. Only you know how often the circumstance of duplication is
likely to occur. Write two sets of test code that reflects your
assumption on quantity and quality of data - one that uses TRY and the
other IF EXISTS - and then keep a close look at your watch.

Michael O
http:/blog.crisatunity.com
 
Michael O said:
For a single insert in your stored procedure when IF EXISTS is true
and/or an index violation is thrown, I don't know.

But if you are talking across the breadth of many rows, each with
their own invocation of your stored procedure - and there were few to
no duplicates then it would definitely be slower to test before
inserting. Only you know how often the circumstance of duplication is
likely to occur. Write two sets of test code that reflects your
assumption on quantity and quality of data - one that uses TRY and the
other IF EXISTS - and then keep a close look at your watch.

Michael O
http:/blog.crisatunity.com
Click to expand...

I should have been clearer. I was wanting to compare Try... Catch... in the
database method code, such as in an ASP.NET app, with IF EXISTS in T-SQL.
So the location of the code is different for either case.
 
Back
Top