SQL Server Performance

Best Performance

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Elcymers, Jul 4, 2011.

  1. Elcymers New Member

    Hi.
    I'm in doubt. A friend of mine said that is better to use the operator = instead of <> on Transact SQL.
    e.g. table TABLEX with the field cflag having space ou '*' in the content.
    The code SELECT * FROM tablex WHERE tablex.FLAG = ' ' has a better performance than SELECT * FROM tablex WHERE tablex.FLAG <> '*'?
  2. Luis Martin Moderator

    Could your friend share with us any documentation?
  3. Elcymers New Member

    Luis, I asked him. He made a course and listened from the teacher. Based on his experience etc. So I sent the question.
  4. Jack Vamvas Member

    An execution plan is useful and also the DDL - with index information.
  5. Luis Martin Moderator

    I was testing and I get the same execution plan, duration, etc.
    For maintenance reasons I'll use =.
  6. Elcymers New Member

    Guys, thanks for the clarification.
  7. satya Moderator

    Technically I don't see any issues in using either of them, its just an ease of programming methods.
    As referred by Luis its always best to refer to the execution plan to see whether the query is optimized enough or not.
  8. Elcymers New Member

    Thanks satya. I heard that SQL, using <>, first loads all the data and after that applies the filter. I could not believe it. I refered to the execution plan and saw no difference. So, I asked the teachers.
    Congratulations for your book! You know it will be published in Brazil?
  9. satya Moderator

    Hey ...thanks for the kind words.
    yes it should be available world-wide, you can try Amazon. If not let me know I can get it shipped thru Publishers site.

    I'm sure the book will come handy for many DBAs out there...
  10. mmarovic Active Member

    Your friend is rigth, because the condition flag='' will never have worse performance than flag <> whatever.

    That query will be faster if there is a non-clustered index starting with flag column and there are aproximatelly less then 10% of rows having flag = '', maybe even less. If there is a clustered index on the table starting with flag (that would not be a good design in most cases) then the query with = would be faster regardless of value distribution in the table.

    The reason is that when sql server locates the first row having specific value in an index it keeps reading next item until it finds a row with different value. Since leaf entries of index can be considered sorted, when it finds different one, sql server knows it already read all items with the specified value.

    In case of non-clustered index, the advantage of reading only index entries matching the criteria does not compensate additional work to follow pointer from index entry to actual row in a table (in case table has clustered index this is value of clustered index) unless it is just a small percent of all rows from the table that is accessed. If that percent is too large, query analyser rather chooses to use cluster index or table scan.
  11. satya Moderator

    Mirko, can you reproduce the same with results... I don't think there isn't a great difference unless the number of rows are in millions.
    Discussion?!?

Share This Page