dans.blog: Lesson Learned: Specifying MSSQL Join Hints...

You know, sometimes query analyzer in MSSQL 2000 just doesn't get things right. I had a query that was taking a very long time to execute in MSSQL. The query on average would take 30-60 seconds to complete execution. The query was running a LIKE search over a text data type. Not ideal by any means, but the execution was mind boggling slow.

Figuring my problem was in the fact that I'm doing a LIKE operation on a text data type over hundreds of thousands of records, I figured that was the main culprit. So one of the very first things I tried was setting up a Full Text Index on the data, so I could do a FTS over the data instead. Much to my chagrin, this really showed no noticeable performance increase (maybe like 2-3 seconds faster, but not nearly enough to warrant making radical changes to implement FTS.)

So where was my problem? I really started studying the execution plan. Everything I looked at seem to imply that the LIKE operator was the problem, and it was mandatory (especially since FTS provided no real performance increase.)

Of course I studying all my indexes and even ran the query through MSSQL 2005's Database Engine Tuning Advisor. It didn't find any additional indexes that would help things out.

All of this had me really confused. Just on a whim, I decided to see what would happen if I tried explicitly define the hint type of joins to do in my query. I really didn't think this would help, since any time I removed the LIKE clauses my query executed as I would have expected.

By default, MSSQL was running a HASH join hint—which seemed normal to me. As soon as I specified a LOOP join hint, my performance increased by leaps and bounds. Queries that were running in 30-60 seconds, were now completing in milliseconds to just a few seconds. This performance was in par with what I was expecting the query's performance to be in the first place.

Original Query:

select
	users.userId, users.name, userNotes.note
from
	dbo.users
		left join
	dbo.userNotes
		on
	userNotes.userId = users.userId
where
  userNotes.note LIKE '%search%'

New Query:

select
	users.userId, users.name, userNotes.note
from
	dbo.users
		left LOOP join
	dbo.userNotes
		on
	userNotes.userId = users.userId
where
  userNotes.note LIKE '%search%'

The lesson here is that while MSSQL's estimated query plan is almost the best one, if a query is not performing the way you expect, look into providing a join hint to see if that fixes the problem.

Categories: SQL, HTML/ColdFusion

6 Comments

Tony PEtruzzi
Oct 3, 2006 @ 9:51 AM

dan,

just curious to see if a derived table would give you similar performance in this situation. Try out the query below and tell me how it worked:

select
    users.userId, users.name, userNotes.note
from
    dbo.users
    inner join
    (
        SELECT userid, userNotes
        FROM dbo.userNotes
        WHERE userNotes.note LIKE '%search%'
    ) AS userNotes
    on userNotes.userId = users.userId
Dan G. Switzer, II
Oct 3, 2006 @ 10:11 AM

@Tony:

I did try variations of derived tables--which didn't seem to help. My actual query was a much more complex join--I simplified my example for readability sakes.

Also, the actual table represented by the "userNotes" table has a 1m+ rows in it, so doing a LIKE on all the rows would be undesirable.

I did just test it against a derived table. While the derived table seems like it might be a little faster than my unhinted joins--it's still much slower than when I specify the join hint.

What's strange is this is the first time I've ever run into a situation where I've come across where I need to specify the join hint.

I suspect it's because of all the different indexes and the number of joins required causing MSSQL to incorrectly guess at the best join methods.
Ben Nadel
Oct 3, 2006 @ 2:21 PM

Dan, I have never even heard of a JOIN hint! Thanks for the heads up on stuff I didn't know. Now I get to go look it up :D
Jochem van Dieten
Oct 3, 2006 @ 2:45 PM

Can you show the real join and the execution plan before and after? It would be interesting to see where the optimizer fails.
Dan G. Switzer, II
Oct 4, 2006 @ 9:19 AM

@Jochem:

I'm not so sure it's that the optimizer fails, but more w/the how the query is actually designed (and there's not much I can do to change the output.)

If you look at the number of executions the default HASH method does less executions--which is probably why MSSQL assumes this is the best plan.

However, because of the strange nature of the query, it's actually much faster to execute a bunch of loops in the join.

In order to get things on an even playing field, I ran both queries with the following lines--which should drop any caching:

dbcc freeproccache
go
dbcc dropcleanbuffers
go

I ran both queries over a 4 year period of data using the same query statement--the only difference being the join hints.

Specifying the LOOP join hint takes approximately 17 seconds to run my complex query (which does multiple LIKE operations.)

If I remove the join hints, MSSQL server takes approximately 60 seconds to execute.

You also notice that the LOOP join immediately starts returning results as their found, where the default HASH join has to execute the entire query before it starts feeding back results.

I honestly wish I knew a little bit more about the different join hints. There's not a lot of information about the different types of hints out there (at least I couldn't find any really good indepth articles when searching Google.)
Brian M
Dec 16, 2006 @ 8:13 AM

I had a similar problem. I was doing a query that was taking over 2 minutes to execute. I would review SQL servers execution plans and look at where the time was going. Everything kept pointing me in the direction of adding a new index etc. which just didn't seem to help.

Like was stated above, on a whim I put in a join hint and the query time went down to less than 1 second! However, no matter if I used a join hint of 'loop', 'merge' or 'hash' I got this dramatic performance improvement. I also must point out that I was joining to a lot of tables in the single query and apparently the query optimizer changes around the order of the joins because the execution plan looked a LOT different. I have yet to compare the three different execution plans with hint to the one without in detail but I need to especially because I wonder why, one of the threee hint options didn't give the same execution plan as not specifying a hint at all.

Anyway here is the one tidbit of information that really makes this interesting and makes me wonder why the query optimizer did so poorly. The join that was having problems had access to an index that contained a LOT of non-unique values. out of 4mil records there were approximately 150 unique values with 4 or 5 of the values having 500,000+ entries each. So, I would re-gen the index, tried clustering it etc. nothing helped. I removed it. still nothing.

Comments for this entry have been disabled.