It really frustrates me when I can put SQL query hints in to make queries go faster. I should not be able to make a better execution plan than the built-in optimizer.
It really frustrates me when I can put SQL query hints in to make queries go faster. I should not be able to make a better execution plan than the built-in optimizer.
There’s a decent chance that optimizing execution paths is an undecidable problem (most questions about programs are), so it’s not surprising that the built-in optimizer isn’t perfect.
No, it certainly wouldn’t be perfect. But it should know much more than I do about the type of data it has and how it’s layed out in storage. There have been some cases where I can understand why it couldn’t figure out the “right” way to get me the requested data, but there are others (like today) where what I was trying to do just wasn’t that complicated, or even an unusual problem for a SQL engine to deal with.
When running it without tweaks takes 7 seconds, and adding the word “loop” to a join makes it take 1, it just seems like there’s something wrong. And once I show it how it could be done, couldn’t it cache that plan as a possibly better way to deal with it in the future, so I don’t need to put the tweaks in production code? (When I don’t know how it’ll be laying out things in memory in the future (or really now for that matter), or if a future version of the database server will do things differently, I’m inclined to not try to outsmart the optimizer and leave hints like that in there.)