The OR logic is used because it conforms to people's thinking habits. It is also quite straightforward to use logical expressions with OR logic to determine the priority and row after the anchor line. However, because of how the SQL Server optimizer works, the OR logic has a performance problem, especially if there are no indexes on some of the filtered columns. For example, consider a filter such as "col1=5 or col2=10". If there are separate indexes on col1 and col2, the optimizer can first filter the travel in each index, and then perform index intersection operations (index intersection) operations on the two results. However, if there is an index on only one of the columns, the index is useless even if the selectivity of the filter is very high. To determine whether a row that does not meet the first filter criteria satisfies the second filter condition, SQL Server still needs to scan the entire table. On the other hand, and logic has a better performance potential. Each expression shrinks the range of the result set. The filtered rows on an index are already a superset of the rows that are ultimately returned. Therefore, any index on the filtered column may have potential utilization value. Whether it is worthwhile to use an existing index is determined by selectivity, but at least it is possible, for example, to consider the filter "col1=5 and col2=10". The best index at this point is the combined index created on these two columns. Then, if you have an index on only one of the columns and the options are high enough, that's enough. SQL Server can filter the data through this index, and then find the rows to check if they also meet the second condition. Use the following logic to filter rows:orderdate> ' 20080506 ' or (orderdate = ' 20080506 ' and OrderID >11075)
equivalent to:Orderdate>= ' 20080506 ' and (OrderDate > ' 20080506 ' and OrderID >11075)
--Experiment as follows:IF object_id (' dbo. Myorders ') is not NULL
DROP TABLE dbo. Myorders;
GO
SELECT * into dbo. Myorders from Sales.orders
CREATE INDEX Idx_dt on dbo. Myorders (OrderDate);
use the OR logical operator:SELECT OrderID, OrderDate, CustID, Empid
FROM dbo. Myorders
WHERE OrderDate > ' 20080506 '
OR (orderdate = ' 20080506 ' and OrderID > 11075);
using the and logical operators:SELECT OrderID, OrderDate, CustID, Empid
FROM dbo. Myorders
WHERE OrderDate >= ' 20080506 '
and (OrderDate > ' 20080506 ' OR orderid > 11075);
After you create a composite index, the effect is moreCREATE INDEX idx_dt_oid on dbo. Myorders (OrderDate, OrderID);
The conclusion is obvious:"1", compared with or logic, SQL Server can better optimize the and logic. Similarly, this transformation can be implemented on other logical expressions. 2, another conclusion is that it is best to create an index on all columns that determine the priority. But the problem is that in a product environment, this is not always possible.
SQL Server Logic Transformation
