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.
select users.userId, users.name, userNotes.note from dbo.users left join dbo.userNotes on userNotes.userId = users.userId where userNotes.note LIKE '%search%'
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.