Which operator to use for better performance when doing queries
- Always try to simplify the logic to minimize the result set returned.
- Which operator used in the WHERE clause makes a huge difference.
Data used
I do not include the usual listing of the data, because this is a real size data with over 100,000 rows.
USE adventureworks;
go
SELECT COUNT(*) AS 'Total rows'
FROM Production.TransactionHistory;
go
SELECT COUNT(*) AS 'Total rows'
FROM Production.TransactionHistory;
Total rows ----------- 113443 (1 row(s) affected)
Equals =
SELECT TransactionID, ProductID, ReferenceOrderID,
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID = 359;
go
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID = 359;
go
TransactionID ProductID ReferenceOrderID ReferenceOrderLineID TransactionDate TransactionType Quantity ActualCost ModifiedDate ------------- ----------- ---------------- -------------------- ----------------------- --------------- ----------- --------------------- ----------------------- 107970 359 589 1 2003-09-18 00:00:00.000 P 3 47.6805 2003-09-18 00:00:00.000 114677 359 835 1 2003-10-07 00:00:00.000 P 3 47.523 2003-10-07 00:00:00.000 117119 359 922 1 2003-10-17 00:00:00.000 P 3 47.523 2003-10-17 00:00:00.000 117578 359 925 1 2003-10-19 00:00:00.000 P 3 47.6805 2003-10-19 00:00:00.000 124940 359 1012 1 2003-11-07 00:00:00.000 P 3 47.6805 2003-11-07 00:00:00.000 137659 359 1164 1 2003-12-14 00:00:00.000 P 3 47.523 2003-12-14 00:00:00.000 145279 359 1325 1 2004-01-05 00:00:00.000 P 3 47.6805 2004-01-05 00:00:00.000 147421 359 1404 1 2004-01-13 00:00:00.000 P 3 47.6805 2004-01-13 00:00:00.000 156295 359 1638 1 2004-02-07 00:00:00.000 P 3 47.523 2004-02-07 00:00:00.000 166430 359 1878 1 2004-03-07 00:00:00.000 P 3 47.6805 2004-03-07 00:00:00.000 168430 359 1954 1 2004-03-14 00:00:00.000 P 3 47.523 2004-03-14 00:00:00.000 176085 359 2194 1 2004-04-04 00:00:00.000 P 3 47.6805 2004-04-04 00:00:00.000 182768 359 2428 1 2004-04-27 00:00:00.000 P 3 47.523 2004-04-27 00:00:00.000 192611 359 2668 1 2004-05-19 00:00:00.000 P 3 47.6805 2004-05-19 00:00:00.000 194737 359 2744 1 2004-05-26 00:00:00.000 P 3 47.523 2004-05-26 00:00:00.000 205506 359 2984 1 2004-06-20 00:00:00.000 P 3 47.6805 2004-06-20 00:00:00.000 209954 359 3218 1 2004-07-07 00:00:00.000 P 3 47.523 2004-07-07 00:00:00.000 211840 359 3458 1 2004-07-26 00:00:00.000 P 3 47.6805 2004-07-26 00:00:00.000 212458 359 3534 1 2004-08-01 00:00:00.000 P 3 47.523 2004-08-01 00:00:00.000 212964 359 3774 1 2004-08-19 00:00:00.000 P 3 47.6805 2004-08-19 00:00:00.000 (20 row(s) affected)
Execution Plan for the = queries
Execution Plan with the cost for the = queries
- This is the most efficient SQL query, especially if the WHERE clause is covered by an index.
Greater than, less than...
SELECT TransactionID, ProductID, ReferenceOrderID,
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID >= 359 and ProductID <= 361;
go
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID >= 359 and ProductID <= 361;
go
TransactionID ProductID ReferenceOrderID ReferenceOrderLineID TransactionDate TransactionType Quantity ActualCost ModifiedDate ------------- ----------- ---------------- -------------------- ----------------------- --------------- ----------- --------------------- ----------------------- 107970 359 589 1 2003-09-18 00:00:00.000 P 3 47.6805 2003-09-18 00:00:00.000 114677 359 835 1 2003-10-07 00:00:00.000 P 3 47.523 2003-10-07 00:00:00.000 117119 359 922 1 2003-10-17 00:00:00.000 P 3 47.523 2003-10-17 00:00:00.000 117578 359 925 1 2003-10-19 00:00:00.000 P 3 47.6805 2003-10-19 00:00:00.000 124940 359 1012 1 2003-11-07 00:00:00.000 P 3 47.6805 2003-11-07 00:00:00.000 137659 359 1164 1 2003-12-14 00:00:00.000 P 3 47.523 2003-12-14 00:00:00.000 145279 359 1325 1 2004-01-05 00:00:00.000 P 3 47.6805 2004-01-05 00:00:00.000 147421 359 1404 1 2004-01-13 00:00:00.000 P 3 47.6805 2004-01-13 00:00:00.000 156295 359 1638 1 2004-02-07 00:00:00.000 P 3 47.523 2004-02-07 00:00:00.000 166430 359 1878 1 2004-03-07 00:00:00.000 P 3 47.6805 2004-03-07 00:00:00.000 168430 359 1954 1 2004-03-14 00:00:00.000 P 3 47.523 2004-03-14 00:00:00.000 176085 359 2194 1 2004-04-04 00:00:00.000 P 3 47.6805 2004-04-04 00:00:00.000 182768 359 2428 1 2004-04-27 00:00:00.000 P 3 47.523 2004-04-27 00:00:00.000 192611 359 2668 1 2004-05-19 00:00:00.000 P 3 47.6805 2004-05-19 00:00:00.000 194737 359 2744 1 2004-05-26 00:00:00.000 P 3 47.523 2004-05-26 00:00:00.000 205506 359 2984 1 2004-06-20 00:00:00.000 P 3 47.6805 2004-06-20 00:00:00.000 209954 359 3218 1 2004-07-07 00:00:00.000 P 3 47.523 2004-07-07 00:00:00.000 211840 359 3458 1 2004-07-26 00:00:00.000 P 3 47.6805 2004-07-26 00:00:00.000 212458 359 3534 1 2004-08-01 00:00:00.000 P 3 47.523 2004-08-01 00:00:00.000 212964 359 3774 1 2004-08-19 00:00:00.000 P 3 47.6805 2004-08-19 00:00:00.000 107971 360 589 2 2003-09-18 00:00:00.000 P 3 45.5805 2003-09-18 00:00:00.000 114678 360 835 2 2003-10-07 00:00:00.000 P 3 45.423 2003-10-07 00:00:00.000 117120 360 922 2 2003-10-17 00:00:00.000 P 3 45.423 2003-10-17 00:00:00.000 117579 360 925 2 2003-10-19 00:00:00.000 P 3 45.5805 2003-10-19 00:00:00.000 124941 360 1012 2 2003-11-07 00:00:00.000 P 3 45.5805 2003-11-07 00:00:00.000 137660 360 1164 2 2003-12-14 00:00:00.000 P 3 45.423 2003-12-14 00:00:00.000 145280 360 1325 2 2004-01-05 00:00:00.000 P 3 45.5805 2004-01-05 00:00:00.000 147422 360 1404 2 2004-01-13 00:00:00.000 P 3 45.5805 2004-01-13 00:00:00.000 156296 360 1638 2 2004-02-07 00:00:00.000 P 3 45.423 2004-02-07 00:00:00.000 166431 360 1878 2 2004-03-07 00:00:00.000 P 3 45.5805 2004-03-07 00:00:00.000 168431 360 1954 2 2004-03-14 00:00:00.000 P 3 45.423 2004-03-14 00:00:00.000 176086 360 2194 2 2004-04-04 00:00:00.000 P 3 45.5805 2004-04-04 00:00:00.000 182769 360 2428 2 2004-04-27 00:00:00.000 P 3 45.423 2004-04-27 00:00:00.000 192612 360 2668 2 2004-05-19 00:00:00.000 P 3 45.5805 2004-05-19 00:00:00.000 194738 360 2744 2 2004-05-26 00:00:00.000 P 3 45.423 2004-05-26 00:00:00.000 205507 360 2984 2 2004-06-20 00:00:00.000 P 3 45.5805 2004-06-20 00:00:00.000 209955 360 3218 2 2004-07-07 00:00:00.000 P 3 45.423 2004-07-07 00:00:00.000 211841 360 3458 2 2004-07-26 00:00:00.000 P 3 45.5805 2004-07-26 00:00:00.000 212459 360 3534 2 2004-08-01 00:00:00.000 P 3 45.423 2004-08-01 00:00:00.000 212965 360 3774 2 2004-08-19 00:00:00.000 P 3 45.5805 2004-08-19 00:00:00.000 104761 361 424 1 2003-09-05 00:00:00.000 P 3 49.6965 2003-09-05 00:00:00.000 107972 361 589 3 2003-09-18 00:00:00.000 P 3 49.854 2003-09-18 00:00:00.000 114679 361 835 3 2003-10-07 00:00:00.000 P 3 49.6965 2003-10-07 00:00:00.000 117580 361 925 3 2003-10-19 00:00:00.000 P 3 49.854 2003-10-19 00:00:00.000 124933 361 1009 1 2003-11-07 00:00:00.000 P 3 49.6965 2003-11-07 00:00:00.000 130674 361 1088 1 2003-12-01 00:00:00.000 P 3 49.854 2003-12-01 00:00:00.000 141291 361 1243 1 2003-12-27 00:00:00.000 P 3 49.6965 2003-12-27 00:00:00.000 145281 361 1325 3 2004-01-05 00:00:00.000 P 3 49.854 2004-01-05 00:00:00.000 149299 361 1483 1 2004-01-20 00:00:00.000 P 3 49.854 2004-01-20 00:00:00.000 156297 361 1638 3 2004-02-07 00:00:00.000 P 3 49.6965 2004-02-07 00:00:00.000 166432 361 1878 3 2004-03-07 00:00:00.000 P 3 49.854 2004-03-07 00:00:00.000 170567 361 2033 1 2004-03-22 00:00:00.000 P 3 49.6965 2004-03-22 00:00:00.000 178428 361 2273 1 2004-04-12 00:00:00.000 P 3 49.854 2004-04-12 00:00:00.000 182770 361 2428 3 2004-04-27 00:00:00.000 P 3 49.6965 2004-04-27 00:00:00.000 192613 361 2668 3 2004-05-19 00:00:00.000 P 3 49.854 2004-05-19 00:00:00.000 199759 361 2823 1 2004-06-02 00:00:00.000 P 3 49.6965 2004-06-02 00:00:00.000 207484 361 3063 1 2004-06-26 00:00:00.000 P 3 49.854 2004-06-26 00:00:00.000 209956 361 3218 3 2004-07-07 00:00:00.000 P 3 49.6965 2004-07-07 00:00:00.000 211842 361 3458 3 2004-07-26 00:00:00.000 P 3 49.854 2004-07-26 00:00:00.000 212625 361 3613 1 2004-08-08 00:00:00.000 P 3 49.6965 2004-08-08 00:00:00.000 213132 361 3853 1 2004-08-25 00:00:00.000 P 3 49.854 2004-08-25 00:00:00.000 (61 row(s) affected)
Execution Plan for the >= <= queries
Execution Plan with the cost for the >= <= queries
- Microsoft SQL Server rewrites a BETWEEN to >= and <=.
- This is the second most efficient SQL query, especially if the WHERE clause is covered by an index.
Like
SELECT TransactionID, ProductID, ReferenceOrderID,
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID like '36%';
go
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID like '36%';
go
TransactionID ProductID ReferenceOrderID ReferenceOrderLineID TransactionDate TransactionType Quantity ActualCost ModifiedDate ------------- ----------- ---------------- -------------------- ----------------------- --------------- ----------- --------------------- ----------------------- 104761 361 424 1 2003-09-05 00:00:00.000 P 3 49.6965 2003-09-05 00:00:00.000 104762 362 424 2 2003-09-05 00:00:00.000 P 3 45.423 2003-09-05 00:00:00.000 104765 365 427 1 2003-09-05 00:00:00.000 P 3 43.4805 2003-09-05 00:00:00.000 104766 366 427 2 2003-09-05 00:00:00.000 P 3 41.3805 2003-09-05 00:00:00.000 104767 367 427 3 2003-09-05 00:00:00.000 P 3 45.654 2003-09-05 00:00:00.000 ... 213132 361 3853 1 2004-08-25 00:00:00.000 P 3 49.854 2004-08-25 00:00:00.000 213133 362 3853 2 2004-08-25 00:00:00.000 P 3 45.5805 2004-08-25 00:00:00.000 213134 363 3853 3 2004-08-25 00:00:00.000 P 3 43.4805 2004-08-25 00:00:00.000 213295 364 3932 1 2004-08-30 00:00:00.000 P 3 47.754 2004-08-30 00:00:00.000 213296 365 3932 2 2004-08-30 00:00:00.000 P 3 43.4805 2004-08-30 00:00:00.000 (210 row(s) affected)
I have removed most of data from the result set.
Execution Plan for the LIKE queries
Execution Plan with the cost for the LIKE queries
- This is the third most efficient SQL query, especially if the WHERE clause is covered by an index.
- If you start with a value, then use the wildcards, such as: LIKE 'E%'
- If you start with a wildcard, then SQL Server will do a table scan, whether there is an index or not, such as: LIKE '%E'
Different from <>
SELECT TransactionID, ProductID, ReferenceOrderID,
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID <> 360;
go
ReferenceOrderLineID, TransactionDate,
TransactionType, Quantity, ActualCost,
ModifiedDate
FROM Production.TransactionHistory
WHERE ProductID <> 360;
go
TransactionID ProductID ReferenceOrderID ReferenceOrderLineID TransactionDate TransactionType Quantity ActualCost ModifiedDate ------------- ----------- ---------------- -------------------- ----------------------- --------------- ----------- --------------------- ----------------------- 100000 784 41590 0 2003-09-01 00:00:00.000 W 2 0.00 2003-09-01 00:00:00.000 100001 794 41591 0 2003-09-01 00:00:00.000 W 1 0.00 2003-09-01 00:00:00.000 100002 797 41592 0 2003-09-01 00:00:00.000 W 1 0.00 2003-09-01 00:00:00.000 100003 798 41593 0 2003-09-01 00:00:00.000 W 1 0.00 2003-09-01 00:00:00.000 100004 799 41594 0 2003-09-01 00:00:00.000 W 1 0.00 2003-09-01 00:00:00.000 ... 213432 347 3996 1 2004-09-03 00:00:00.000 P 3 43.2705 2004-09-03 00:00:00.000 213433 348 3996 2 2004-09-03 00:00:00.000 P 3 41.1705 2004-09-03 00:00:00.000 213434 349 3996 3 2004-09-03 00:00:00.000 P 3 45.444 2004-09-03 00:00:00.000 213435 928 3997 1 2004-09-03 00:00:00.000 P 550 32.7705 2004-09-03 00:00:00.000 213436 929 3997 2 2004-09-03 00:00:00.000 P 550 37.086 2004-09-03 00:00:00.000 213437 930 3997 3 2004-09-03 00:00:00.000 P 550 43.0395 2004-09-03 00:00:00.000 213438 332 3998 1 2004-09-03 00:00:00.000 P 550 10.815 2004-09-03 00:00:00.000 213439 907 3999 2 2004-09-03 00:00:00.000 P 550 82.8345 2004-09-03 00:00:00.000 213440 948 3999 1 2004-09-03 00:00:00.000 P 550 82.8345 2004-09-03 00:00:00.000 213441 325 4000 1 2004-09-03 00:00:00.000 P 1250 0.21 2004-09-03 00:00:00.000 213442 326 4000 2 2004-09-03 00:00:00.000 P 1250 0.21 2004-09-03 00:00:00.000 (113423 row(s) affected)
I have removed most of data from the result set.
Execution Plan for the <> query
Execution Plan with cost for the <> query
- Microsoft SQL Server rewrites a NOT to <>.
- SQL Server will do a table scan, whether there is an index covering the WHERE clause or not.
Conclusion
Here is the order of efficiency:
- =
- >, >=, <, <=, BETWEEN
- LIKE
- <>, NOT
Use = as much as possible. Use <> as least as possible.