Even when columns are indexed and the result set returned is only one row, SQL Server can choke. In my case, analyzing the query showed that it was attempting to scan the entire table for both the ">" selection and the "<" selection. Converting these to sub-selects did nothing to prevent the full table scans. Watch out for this!
SELECT
t1.col1, t1.col2 t1.col3,
t2.col1, t2.col2, t3.col3
FROM
table1 t1, table2 t2
WHERE
t1.col1 = 123456
AND
t1.col2 > t2.col2
AND
t1.col3 < t2.col3
Feedback About this Tip
Shawn H. points out that "the reason the query table scanned was probably
because of the > t2.col3. If an index existed on that column you would need to modify the query to a >= t2.col3 or add an index and do the same. I would be willing to bet that this is the point where the server decided a table scan was the query plan of choice because > t2.col3 is not giving it a starting point in an index therefore the query optimizer decides a table scan is in order.
This query also is not doing a range. Every column in the WHERE is
different. A range would have been as follows:
SELECT
t1.col1, t1.col2 t1.col3,
t2.col1, t2.col2, t3.col3
FROM
table1 t1, table2 t2
WHERE
t1.col1 = 123456
AND
t1.col2 > t2.col2 this should be t1.col2 >= t2.col2
AND
t1.col2 < t2.col2
For More Information
- What do you think about this tip? E-mail us at tdichiara@techtarget.com with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
