Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,619

    Unanswered: Validate vs Try/Catch

    Hi guys,

    Quick question on SQL best practise,... should you validate data in a stored procedure before calling an insert or update and throw exceptions when needed or, should you wrap your insert or update in a try-catch block, then check the exception that is caught (eg. foreign key constraint) then throw a new user friendly exception?

    In the validate first example, you would still rely on the foreign key constraints, they would still exist but they would be a fall back. You would try to make sure everything is correct before the insert or update and throw friendly messages.

    The in second example you assume all data is correct, perform the action inside the try catch block and then, if an exception did occur try to essentially override it with a user-friendly version of the exception.

    Any advice to offer in this area?

    Cheers

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,824
    Provided Answers: 14
    It depends.

    If you are looking at an OLTP system, where you are inserting single rows, and need each transaction to run very fast, it may be best to go with the catching the foreign key error, instead of incurring the lookup (twice, in fact) for each insert. Less locking, quicker insert, happier end users.

    If you have an OLAP system, where you are inserting large batches, and are worried about the rollback time, it would probably be best to do the validation check beforehand. It takes a little longer, but you save on time rolling back the transaction, and re-doing the insert. Testing would need to be done to see if the time spent on the validation check is worth it, of course.

  3. #3
    Join Date
    Oct 2007
    Posts
    92
    Provided Answers: 6
    I have always preferred letting database handle the constraints. I guarantee it will do RI checks and check constraints faster than you will within your code. Very easy to code for the specific RI/Check violation. I have worked on systems, where they had no RI defined for "speed", but they invariably had issues with bad/missing data(childless/orphan data), same with some data that had constraints where, all of a sudden someone would notice that this Y/N field had spaces or an F.

    Dave Nance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •