I've had to use hints from time to time on stored procedures in SQL Server. I had to use WITH RECOMPILE more than once because of the server generating query plans that would take 60 seconds for one input and .002 seconds on another. The plan seemed to be optimizing for a weird case that made no sense for the general case. Adding that option made it work in .002 seconds for every call. I never noticed plan generation overhead doing this.
In MySQL I've had to use FORCE INDEX usually when sorting on a large set where the primary key was used on the table or was available for sorting but when that wasn't what I needed it for. I used force index on the column I was sorting by and that improved performance by around 50% maybe but then I ended up caching the results because the worst case was still too slow for my taste and they didn't need to change as often as I was executing this query (it was a random sample of the latest actions on a site).
These are edge cases I suppose but I'm glad they're there for me when I need them. I respect their reasons for not including them though as I normally see them used incorrectly or just strangely. (e.g. insert into table (nolock) (id, blah)...)
You can take NOLOCK out of my cold, dead, hands! That one is a lifesaver for reading from terabyte-sized OLTP tables with 100/sec inserts...
I really like what the guy is saying in the article. However, it's the perennial idealism vs pragmatism argument. And, i'll put my hand up for pragmatism: I've needed hints in the past, because like everything else in this world, query planners simply aren't perfect.
In MySQL I've had to use FORCE INDEX usually when sorting on a large set where the primary key was used on the table or was available for sorting but when that wasn't what I needed it for. I used force index on the column I was sorting by and that improved performance by around 50% maybe but then I ended up caching the results because the worst case was still too slow for my taste and they didn't need to change as often as I was executing this query (it was a random sample of the latest actions on a site).
These are edge cases I suppose but I'm glad they're there for me when I need them. I respect their reasons for not including them though as I normally see them used incorrectly or just strangely. (e.g. insert into table (nolock) (id, blah)...)