Post reply

Odd plan differences betwen LEFT JOIN and NOT EXISTS on insert

  • August 6, 2014 at 6:50 am

    #310549

    Hi,

    I have some code that is currently using the LEFT JOIN WHERE NULL pattern to filter out records that already exist when doing an insert . I am looking at changing this to a WHERE NOT EXISTS instead but looking at the two plans that get generated they are quite different (attached).

    The plan using the WHERE NOT EXISTS is doing index inserts as well (which seems reasonable as I'm inserting data) the LEFT JOIN doesn't do this which doesn't actually seem particularly correct. Taking out the insert so just doing the select gives near identical plans except the filter operator that I'd expect.

    Could someone give me some pointers as to why just changing the select (but still logically the same) is giving such a different plan on insert?

    Thanks

    Leyton

  • August 6, 2014 at 7:56 am

    #1735647

    You can read about per-row and per-index updates in this article by Paul White.

    I'll take a wild-ish guess and suggest that if you refresh statistics on tblPersonOtherAttribute, the plans for the two queries will be the same.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply