Best Performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Best Performance

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 <> ‘*’?
Could your friend share with us any documentation?
Luis, I asked him. He made a course and listened from the teacher. Based on his experience etc. So I sent the question.
An execution plan is useful and also the DDL – with index information.
I was testing and I get the same execution plan, duration, etc.
For maintenance reasons I’ll use =.
Guys, thanks for the clarification.
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.
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?
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…
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.
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 <> ‘*’?
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?!?
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |