Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: SQL Server error handling

    Hi

    I think by virtue of not being able to find reference to this I have my answer however....

    You trap an error, check it and know that you are happy with it - it isn't an issue. Is it possible to prevent that specific error (number, message - the lot) being passed to the client so developers don't need to handle the error a second time?

    Ta db chucks
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You mean something radical, like preventing the error from occuring in the first place instead of just trapping it via a catch?

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    You mean something radical, like preventing the error from occuring in the first place instead of just trapping it via a catch?
    Lol - your new fangled ideas bear little stock here.

    Most errors do, of course, indicate an error and therfore you do what you can to avoid them. The specific senario I had in mind was actually one of Brett's suggestions that I have seen him post once or twice (a la http://www.dbforums.com/t1191236.html). Since you are deliberately coding in a manner that risks an error but reduces IO it would be nice to handle the result of no inserts as gracefully as you could if you used the high IO method and checked @@RowCount.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, responding to that specific case:
    Code:
    UPDATE myTable99
       SET Col3 = 'a'
       WHERE  1 = Col1
          AND 1 = Col2
    
    IF 0 = @@rowcount
       INSERT INTO myTable99(Col1,Col2,Col3)
          SELECT 1,1,'a'
    No error, no double IO. Nothing but net!

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your code is kind of a half way house yes? In the event of there not being a matching value then it is no improvement??
    Code:
    SET NOCOUNT ON
     
    CREATE TABLE dbo.myTable99
     (Col1 int, 
     Col2 int, 
     Col3 char(1), 
     PRIMARY KEY (Col1, Col2))
     
    INSERT INTO dbo.myTable99(Col1,Col2,Col3)
    --SELECT 1,1,'a' UNION ALL
    SELECT 1,2,'b' UNION ALL
    SELECT 1,3,'c'
     
    PRINT 'Brett:'
    SET STATISTICS IO ON
     
     INSERT INTO dbo.myTable99(Col1,Col2,Col3)
     SELECT 1,1,'a'
     
    SET STATISTICS IO OFF
     
    TRUNCATE TABLE
     dbo.myTable99
     
    INSERT INTO dbo.myTable99(Col1,Col2,Col3)
    --SELECT 1,1,'a' UNION ALL
    SELECT 1,2,'b' UNION ALL
    SELECT 1,3,'c'
     
    PRINT 'Pat:'
    SET STATISTICS IO ON
     
     UPDATE myTable99
        SET Col3 = 'a'
        WHERE  1 = Col1
           AND 1 = Col2
     
     IF 0 = @@rowcount
      INSERT INTO dbo.myTable99(Col1,Col2,Col3)
      SELECT 1,1,'a'
     
    SET STATISTICS IO OFF
     
    TRUNCATE TABLE
     dbo.myTable99
     
    INSERT INTO dbo.myTable99(Col1,Col2,Col3)
    --SELECT 1,1,'a' UNION ALL
    SELECT 1,2,'b' UNION ALL
    SELECT 1,3,'c'
     
    PRINT 'Classic:'
    SET STATISTICS IO ON
     
     INSERT INTO 
      dbo.myTable99(Col1,Col2,Col3)
     SELECT  1,1,'a'
     WHERE NOT EXISTS( SELECT  * 
        FROM  dbo.myTable99
        WHERE Col1 = 1  
         AND Col2 = 1)
     
    SET STATISTICS IO OFF
     
    DROP TABLE dbo.myTable99
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Not really, the UPDATE operation does an index strobe and fails if there is no row to update. As a by product, that refreshes the page statistics, which the INSERT would have to do anyway, so the INSERT only has to check the log for recently added rows. In effect, you get the failed UPDATE for free because all of the work that it does would have needed to be done anyway, and the optimizer is smart enough to realize that (since SQL 2000 sp1 anyway).

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tks Pat.... can't help but feel that NZDF principles are at work here....

    Out of curiosity - would your short answer to the original question be "no - you can't"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    You trap an error, check it and know that you are happy with it - it isn't an issue. Is it possible to prevent that specific error (number, message - the lot) being passed to the client so developers don't need to handle the error a second time?
    I don't know of a way that pure Transact-SQL can suppress a SQL error message at the client once the error has occured. There are many ways to prevent the error, but those all require some form of "smoke and mirrors" to make that happen (the most straightforward way is an extended stored procedure, but there are lots of devious ways too).
    Quote Originally Posted by pootle flump
    Tks Pat.... can't help but feel that NZDF principles are at work here....
    I'm not sure what you mean by that... If you mean that I'm trying to teach something, you're "spot on". If you are concerned that I'm "leading a merry chase", no, not at all.

    If you think about how you'd code the UPDATE / test / INSERT sequence using your favorite scripting language (not SQL, but vba, PHP, Perl, or something similar) against a text file, that should make things clearer. The UPDATE has to find the row of interest in order to update it. If the UPDATE fails, you've already done 80% or more of the work needed to "green flag" the INSERT operation. This makes the INSERT fast and easy compared to doing the same INSERT from a "cold start" because everything that is needed is "fresh and hot" in cache, and the database engine has just done much of the work needed for the INSERT in order to find out that the UPDATE didn't update any rows.

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cheers Pat - I did mean you were teaching me things

    Presumably, then, the advantages of the method you have shown are not observable with STATISTICS IO. Can it be empirically proved by some other measurement? (not doubting you - just learnin' stuff )
    Do you go to this sort of trouble to optimise an insert or are you just running with the thread?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There's always the "brute force" approach to prove it... Time test the little bugger. Test this with updates, then test this with inserts, then test pure inserts (no UPDATE or check of rowcount), and compare the times! The "failed UPDATE" operations have always been "free" when I've checked.

    This is a method I worked out to help manage performance issues through brute force in a really "interesting" schema that I inherited in the mid 1990s. There were many compound keys, lots of "interesting" clustered index choices, etc. that were just killing performance. I was able to prove to myself (via timing tests) that thousands of insert/updates using this methodology could be processed in the same amount of time that six insert/updates could be processed using the old methodology. This had the side benefit of not requiring a round trip client-to-server too, which was very "time expensive" in those days.

    -PatP

Posting Permissions

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