Professional Web Applications Themes

Is it style or for performance? - Microsoft SQL / MS SQL Server

Hi! Found this peace of code and wonder if it gives any advantage...: IF EXISTS (SELECT 1 FROM A Where AKey = _iKey) BEGIN DELETE FROM A Where AKey = _iKey END Personally I would just write: DELETE FROM A Where AKey = _iKey I wonder if it gives any performance in case no record exists? What is the deal here?...

  1. #1

    Default Is it style or for performance?

    Hi!

    Found this peace of code and wonder if it gives any advantage...:

    IF EXISTS (SELECT 1 FROM A Where AKey = _iKey)
    BEGIN
    DELETE FROM A Where AKey = _iKey
    END


    Personally I would just write:
    DELETE FROM A Where AKey = _iKey


    I wonder if it gives any performance in case no record exists?

    What is the deal here?


    Ivan Guest

  2. #2

    Default Re: Is it style or for performance?

    It can if there's some very complex FOR DELETE trigger on table A. In other
    cases I think it's not
     


    Alex Guest

  3. #3

    Default Re: Is it style or for performance?

    Hi Greg,

    I agree with you on the meaningfulness of the existence check.
    Doing a select first can improve performance however, specially if the table
    that is to be deleted from is referenced by foreign keys (see the example at
    the end and check the execution plans). This of course assumes that it is
    reasonably common that the exists returns false, otherwise it is only
    unnecessary overhead.

    In most cases in my experience this kind of code is written by people who
    don't fully understand SQL, and check that there is something to delete
    before they delete it, otherwise there might be an error, according to their
    mental model (which is probably somewhat similar to a flat file/directory
    structure).

    You can however as I said before use this code in certain cirstances to
    improve performance, and in that case you definitely _don't_ want to make
    sure that the existence check is reliable by using repeatable read. If
    someone else deletes the rows between the existence check and the delete,
    though luck, you're doing your delete for nothing, but it probably doesn't
    justify taking out the locks.

    Example:

    delete from employees where employeeid = -1
    select * from employees where employeeid = -1

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Greg Linwood" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Jacco Guest

  4. #4

    Default Re: Is it style or for performance?

    Thanks to all replies.

    This is exact code I prvided (table name different)

    No foreign key or triggers here, plain table.

    Looks like it's extra code...


    "Ivan Demkovitch" <id> wrote in message
    news:phx.gbl... 


    Ivan Guest

Similar Threads

  1. Replies: 0
    Last Post: April 26th, 07:06 PM
  2. performance and style advice requested
    By Alex Martelli in forum Ruby
    Replies: 48
    Last Post: September 17th, 05:44 PM
  3. Replies: 2
    Last Post: September 17th, 06:50 AM
  4. Replies: 0
    Last Post: September 16th, 02:11 AM
  5. Replies: 0
    Last Post: September 16th, 12:49 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139