SQL Server Performance

Using "not equal" instead of "different" operators

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by EMoscosoCam, Aug 27, 2008.

  1. EMoscosoCam Member

    Hello
    I have just read that the "=" has a better performance than "<>", which makes me think that I should use "NOT =" when trying to express inequality. Now, if it is so, why the query engine does not do that internally? In other words, should not the operators "=" and "<>" perform the same?
    Thanks a lot.
  2. FrankKalis Moderator

    Apart from the fact that this syntax is not allowed, we will never know what the query engine is really doing internally. Also, what would be the difference between NOT = and <>, if it was permitted at all?

    I don't think it is correct to say that one operator has "better performance" than another. Actually I think an operator as such has no performance aspect at all. It depends on your query, what you want to see as the result and what your data looks like.
  3. EMoscosoCam Member

  4. HanShi New Member

    For performance it is probably better to split "value1 <> value2" like this: "value1 < value2 AND value1 > value2"
    In this way SQL has better change to benefit from indexes (if exists).
  5. Madhivanan Moderator

    [quote user="HanShi"]
    For performance it is probably better to split "value1 <> value2" like this: "value1 < value2 AND value1 > value2"
    In this way SQL has better change to benefit from indexes (if exists).
    [/quote]
    Did you mean this?
    value1 < value2 or value1 > value2
  6. HanShi New Member

    [quote user="Madhivanan"]
    Did you mean this?
    value1 < value2 or value1 > value2
    [/quote]
    Yes, off course, my mistake[:S]
  7. moh_hassan20 New Member

    i did the following query , with execution plan. i comment every query with the result of plan:
    using "="
    --index seek
    --relative to batch 11%
    --subtree cost 0.003
    --estimated IO cost 0.003
    --estimated rows: 1

    select * from Production.Product
    where ProductID =5
    -----------------------------------------------------------------------
    using "<>"
    --index seek
    --relative to batch 44%
    --estimated IO cost 0.012
    --subtree cost 0.012
    --estimated rows: 503

    select * from Production.Product
    where ProductID <>5
    -----------------------------------------------------------------------------
    using < value1 or > value2
    --index seek
    --relative to batch 44%
    --estimated IO cost 0.012
    --subtree cost 0.012
    --estimated rows: 503
    select * from Production.Product
    where ProductID <4
    or ProductID >6

    conclusion:
    - all cases activate index seek , so no performance lost
    -you find "<>" equivalant to "< value or > valu" in performance.
    - using "<>" operator has no disadvantage , the only effect is the expected number of rows and consequently estimated IO cost
    - it seems "=" better , but realyy it is not , bez it returns only one row , with 0.003 cost , but others returm more than one row and consequently do more IO
    -if query return less rows , that is best IO

    so, don't bother yourself with "=" or "<>"

Share This Page