In my previous blog post on "FOR ALL ENTRIES IN itab" vs SELECTs with LOOPs on itabs, I documented a remarkable reversal in efficiency between two tests - one against the SAP-delivered table GLPCA and one against the SAP-delivered table COBK. Even after I took Anton's suggestion to add BYPASSING BUFFER to the selects, and even after I changed the COLLECT to a hashed read/insert operation, the reversed results persisted: a) against COBK, the "FOR ALL ENTRIES IN itab" was better by an order of magnitude (tenths of seconds vs seconds) b) against GLPCA, the SELECT within LOOP on itab was better by a simialr order of magnitude (tenths of seconds vs seconds). So I turned on SQL trace via ST05 and lo and behold - an explanation immediately appeared. In the GLPCA case: a) the SELECT within LOOP generated fetches on 3 matching columns of index 1; b) "the FOR ALL ENTRIES IN itab" generated fetches on only 2 matching columns of index 1. But in the COBK case, both methods generated fetches on 3 matching columns of index 0, i.e. the primary key. So - what have we learned from this example? Well I think it's fair to say that we've merely re-validated the two oldest proverbs in the IT bible: a) there's no such thing as a free lunch; b) pay me now or pay me later. In other words, "neat" SQL syntactic constructs are not panaceae that can always be trusted and developers must still do due diligence on every piece of code they write for back-end retrievals. Some, particularly the younger set here, will ask: "But Grandpa - the CPU's are so fast now and we can throw so many of them at any given problem, why should we care?" Well, there's two answers to this question. First - the original answer from a very wise IT guru whose name has been forgotten: "A CPU second wasted can never be retrieved". Second - as time goes by and Enterprise SOA becomes the GAWODB (generally accepted way of doing business), I predict that the BW paradigm for OLAP will be supplanted by a different paradigm in which real-time OLAP is made possible by heavy use of alternative indices accompanied by really fast tricks that permit updating of these indices in real time. And as this paradigm gains ascendancy, focus will shift off the bells and whistles of data processing, where it has been since Bill and Larry redefined the field to their own commercial advantage, and back to the basics. (This second point, BTW, is the reason why this post is a separate blog entry, and not a comment on my previous entry. I wanted to make sure that the prediction was seen, and not buried in the details of a technical discussion.) Well, I gotta go code a function module now, so I won't have time to tell y'all the story of Bill Mann and "invisible fields" in Model 204 (the US precursor of ADABAS, which SAP had the good sense to purchase.) It's a great story and it pertains to the prediction made above, so I hope no one will mind if I recount it in my next post ...
