@thistooshallpass said:@Scarlet Manuka said:There would be no measurable performance impact
(For the last, we're talking about looking up and caching about a dozen values based on an index, in a query which is aggregating income information for hundreds of thousands of customers.) So basically you would do 12x100000 lookups, versus 0 with a CASE. And that's for each concurrent user of that query. Yeah, I don't see why there should be a problem. As for caching... you do know that this does require system resources (mostly RAM), that could have been used for something useful? Of course when the server is slow, just add RAM... Typical.
No, the 12 values would be looked up once, read and cached in memory, and then hash joined in as required, usually two or three per customer. And my point is that the cost of hash joining in that value is trivial compared to the cost of looking up all the other stuff we're looking up per customer. That applies to the RAM cost almost as much as it does to the I/O cost.
In addition, this query is a manual one being run by one person. If it gets deployed as a standard report (I don't know if this is planned) there wouldn't be more than a few people using it at most, and probably not at the same time. If I was in charge of database resourcing, which I'm not, I'd be more worried about the stuff that's accessible to thousands of people. Either way, if your database is so close to the edge that adding one very small join to an existing query can cripple it, I'd suggest it probably does need more resources anyway (what are you running it on, your laptop?). I only came across this one because a different subquery of the same parent query, which was being run across a database link, had somehow spawned itself multiple times with extremely weird optimiser hints which were in fact killing performance (running for hours or days instead of 20 seconds), and when it got up to a dozen or so concurrent copies of itself it started affecting database performance for a small number of other queries.
But why take my word for it, when we can do an unscientific test? I created a version with the join in place instead of the sub-cases (and no other changes), and ran both approximately simultaneously; then I did it again to get a reading based on data already being cached, since data caching could have affected the first run in either direction. First run, the query using the join beat the query using sub-cases by 31 seconds (2:53 vs 3:24). On the second run, the query using the join won by 34 seconds (2:44 vs 3:18). So no, I don't see the performance problem with using the join. If it takes a little more RAM, that's a fair price to pay for a 15-17% decrease in run time.