January 18, 2008 at 5:52 am
I have a rather large and very slow query. It joins many tables. Some of the joins are done using functions
Eg.
Select........
From tableA as a join TableB as b on SUBSTRING(a.Col1,2,2) = b.Col2
OR
isnull(a.col1, '') = b.Col2
Note this is an extreemly simplified version of the query and the joins using functions happen many times, as does the usage of OR operators.
I have ensured all indexes are present and correct, and defragmented, yet the query still runs horribly slowly.
The table in question (tableA) does not have too many rows , maby 10,000, so an index wouldnt make too much difference anyway IMO.
Could the functions used in the joins, and the OR operators be causing the slowness of this query? As a general rule of thumb, should I try not to use OR and functions in joins?
January 18, 2008 at 6:39 am
functions in joins cause your perdicates to be NON-SARGable.
meaning indexes will not be used to serve the condition itself. (at least not that optimal)
These conditions will have to be handled later on in the query process.
less optimal paths will be needed to fulfill the set selection, more memory will be needed to handle the filtering because a subset needs to be made to start from to apply the funtion-conditions.
tempdb may also be needed heavy because of materialisation (storing subsets, intermediate results,...).
Avoid performing hidden joins using functions. you will pay for it !
In many cases a normal join can help you out
In other cases CTE's are a very good alternative (and reusable as a set within the query itself)
- Investigate your execution plan , xml-execution plan (very nice 😎 )
- use set stats io on
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2008 at 8:53 am
One possible way to improve performance here is to create calculated columns and index them. This would negate the need to use functions on the join columns - which as already pointed out voids the use of indexes and statistics by the optimizer.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply