Where is it best to place "where conditions" in the join or inthe final where statement

  • Looking at this query I'm not sure why you don't just use a self join. Maybe I don't know the data very well, but it seems that doing a Not Exists with a Not like could be re-written as an inner join.

    select rb.*

    from rebate rb

        join rebate rb2 ON rb.group = rb2.group

            and rb.unit = rb2.unit

            and rb.enterprisecode = rb2.enterprisecode

            and rb.programcode = rb2.programcode

            and rb2.incentiverevcode = 0

            and rb2.sys_nam like '%odsbeta%'

            and rb.unit > ' '

            and rb.file_dte = '2004-05-02'

            and rb.sys_nam like '%odsbeta%'

            and rb.incentiverevcode = 1

     

    I agree wholeheartedly with what Kenneth said above. You really don't want to use the like with a leading wildcard unless absolutly nessecary as it always causes a table scan.

     




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • When I tested it on two of my biggest table (one i 665 000 rows and the other 489 000), "not exists" was faster.

    Regarding "not in"; I have had huge problems with it in the past, I find that I always get unreliable results, so "not exists" is the way to go imo.

  • BJure,

    When you say you tested "IT" and NOT EXISTS was faster, what is "IT"?  In other words, what was "NOT EXISTS" faster than?  Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Oh, sorry.

    My previous post was a reply to your long post (post nr.9) regarding outer joins. I have never thougt of trying that, so I did some testing. But my conclusion was that NOT EXISTS did the job better.

    But I never realized that there was a second page, so I just repeated what everyone else had already said .

  • As already been said, there is no definite 'way to go' - it always depends. It may not be 50-50, but sometimes not exists is better, other times a not in will do the stuff faster.

    The major players are your data and what you want to get from it in conjunction with the indices present.

    /Kenneth

Viewing 5 posts - 16 through 19 (of 19 total)

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