March 8, 2015 at 10:22 am
Can anyone help me tune this query? Name column is nonclustered index with include column, ID and ParentID
SELECT DISTINCT .[ID], .[ParentID]
FROM [Items]
WHERE LOWER(.[Name]) = 'Test'
execution plan is attached.
Thank you!
March 9, 2015 at 7:01 am
Grace09 (3/8/2015)
Can anyone help me tune this query? Name column is nonclustered index with include column, ID and ParentIDSELECT DISTINCT .[ID], .[ParentID]
FROM [Items]
WHERE LOWER(.[Name]) = 'Test'
execution plan is attached.
Thank you!
So that's a picture of an execution plan, not the plan itself. To post the plan, you need to save it as a .SQLPLAN file and then you can attach that. Right click on the plan and choose "Save As" from the context menu.
However, with such a simple query, let me help. The function, LOWER, on the column like that, is a common code smell. It forces the optimizer to do a scan where it could do a seek. If you want to run the query faster, either get rid of the function completely, or, run it on the string, 'Test', instead of on the column.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2015 at 7:37 am
Grant Fritchey (3/9/2015)
Grace09 (3/8/2015)
Can anyone help me tune this query? Name column is nonclustered index with include column, ID and ParentIDSELECT DISTINCT .[ID], .[ParentID]
FROM [Items]
WHERE LOWER(.[Name]) = 'Test'
execution plan is attached.
Thank you!
So that's a picture of an execution plan, not the plan itself. To post the plan, you need to save it as a .SQLPLAN file and then you can attach that. Right click on the plan and choose "Save As" from the context menu.
However, with such a simple query, let me help. The function, LOWER, on the column like that, is a common code smell. It forces the optimizer to do a scan where it could do a seek. If you want to run the query faster, either get rid of the function completely, or, run it on the string, 'Test', instead of on the column.
Also on a case sensitive column or database
LOWER(.[Name]) = 'Test'
will never be true
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2015 at 8:01 am
Since the constant side of your where predicate is not lower case I assume you don't have a case sensitive collation. That means you could greatly increase the performance just by removing the LOWER function. Or if you do have a case sensitive collation but want all rows regardless of case you could specify an insensitive collation in your query. Either of those approaches would get rid of that nonSARGable function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply