where 1=-1 and 1=1 will affect query efficiency?

Source: Internet
Author: User



Today, when using SQL Profiler with a low-level generated SQL, follow this piece of code:


With Tempquery as (SELECT *, Row_number () Up (ORDER by Createtime DESC) as ' Rownumberforsplit ' from (select E.name as Name, U.realyname as realyname,c.[description] as descriptions, ' job seeker ' as TSF, c.result,c.createtime from [Mr]. [User_complaint] UC INNER JOIN [Mr]. [User] U on UC. Usercode=u.code INNER JOIN [Mr]. [Complaint] C on Uc.complaintcode=c.code inner join [Mr]. [Enterprise] E on UC. Enterprisecode=e.code Union Select E.name as Name, U.realyname as realyname,c.[description] as descriptions, ' Enterprise ' as TSF, C . Result,c.createtime from [Mr]. [Enterprise_complaint] EC INNER JOIN [Mr]. [Enterprise] E on EC. Enterprisecode=e.code INNER JOIN [Mr]. [Complaint] C on Ec.complaintcode =c.code inner join [Mr]. [User] U on EC. Usercode=u.code) Cpwhere 1 = 1 and 1=1) SELECT * from Tempquery WHERE rownumberforsplit between 1 and 10;  Select COUNT (1) as Total_count from (select E.name as Name, U.realyname as realyname,c.[description] as descriptions, ' Job seeker ' As TSF, c.result,c.createtime from [Mr]. [User_compLaint] UC INNER join [Mr]. [User] U on UC. Usercode=u.code INNER JOIN [Mr]. [Complaint] C on Uc.complaintcode=c.code inner join [Mr]. [Enterprise] E on UC. Enterprisecode=e.code Union Select E.name as Name, U.realyname as realyname,c.[description] as descriptions, ' Enterprise ' as TSF, C . Result,c.createtime from [Mr]. [Enterprise_complaint] EC INNER JOIN [Mr]. [Enterprise] E on EC. Enterprisecode=e.code INNER JOIN [Mr]. [Complaint] C on Ec.complaintcode =c.code inner join [Mr]. [User] U on EC. Usercode=u.code) CP WHERE 1 = 1 and 1=1

And then you see where 1=1 and 1=1, followed by, have used this thing before, but later someone said it affected the performance of the query, and then some people said no impact. Then I was lost ...



Let's do an experimental test by myself.


First, let's look at a query without this condition:


/****** Script for selecttopnrows command from SSMS  ******/select TOP 100000 [resource_id]      , [CLASS]      , [Sort_ ID]      , [xml_content]      , [search_content]      , [row_id] from  [mcs_workflow].[ WF]. [Generic_form_relative_data] WHERE 1=1 and 1=1

Then use the execution plan to estimate the following:





Then add the conditions:





As you can see in the execution plan, the overhead is almost entirely on the scan of the clustered index table, and the two table data are found to be consistent.





Hey, it seems that their query efficiency is the same.


But why???? Baidu under the bar ....







where 1=-1 and 1=1 will affect query efficiency?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.