[snip]
>The benefit becomes even greater if the intermediate result is useful in creating several independent queries (eg a net sales report, a salesmen's commission report, an inventory activity report and a cost of goods sold report may all share the common set of inventory transactions over a period of time, so pulling that subset separately and using it in each report's SELECT, especially when we explicitly build useful indexes.) The more repetitive reference to a given subset, either in the body of several queries, or in establishing reusable subselect sets, the higher the payout.

Thanks both to you and Ellen. This starts making sense. Even when my definition of intuition involves "your regular brain working in the background, just being too fast to follow", still I prefer knowing the logic it is based on.

My most dramatic example was exactly a reusable intermediate result - a series of about hundred joins on calendar data vs calendar data was run in order to create a series of matches, and it turned out to be much faster once I pulled the month it was supposed to work on into an intermediate cursor - actually two of them, just in order to have them have two distinct names for clarity's sake. The factor ranged anywhere between 10 and 20 (30 or more minutes down to 2-3).