3 ms·
A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maint
by DrJokepu 7y ago
A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maintain than a giant query, with or without CTEs or hints.
- topspin 7y agoIndeed. I too have resorted to implementing my own plan using temporary tables. Easier to maintain though? I can't recall ever resorting to this technique and thinking it was a maintenance improvement. When what might have been a single query evolves into a facade to hide the temporary table gymnastics I always feel it's at least a maintenance setback.
- radiowave 7y agoYes, I regard temporary tables as a last-resort. Although, a sometimes very useful thing that this approach has going for it is that you can add indexes to those temporary tables which are much more specialized than what could be achieved by working solely with the indexes of the source tables, no matter how sophisticated the query plan.
- DrJokepu 7y agoMaybe it’s a subjective thing, for me at least I think breaking up a larger problem into a series of smaller problems tends to make it easier to understand what’s going on.