SQL Server is doing the conversion BEFORE any filtering. Since you are OR'ing the criteria I would recommend using UNION ALL like this:
SELECT
M.modelid
FROM
dbo.Models AS M INNER JOIN
dbo.ModelAttributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(
MA.AttributeID = 29 AND
MA.Value LIKE N'%IL%'
)AND
(M.ProductID = 2)
UNION ALL
SELECT
M.modelid
FROM
dbo.Models AS M INNER JOIN
dbo.ModelAttributes AS MA
ON MA.ModelId = M.ModelID
WHERE
(
MA.AttributeID = 7 AND
(
CONVERT(DECIMAL(10, 5), MA.Value) >= 0.0360 AND
CONVERT(DECIMAL(10, 5), MA.Value) <= 0.0840
)
) AND
(M.ProductID = 2)
I also eliminated the unecessary conversion to NVARCHAR(128) in the first comparison. By using the conversion function you are keeping the optimizer from considering an INDEX SEEK.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question