Large database FormulaFill SuperLookup statements can be time consuming. One speedup option PanX offers is the “Wrap” option. I’m trying to leverage it into more circumstances.
Suppose database dbTo (with t records and including fields keyTo and valueTo) is doing a FormulaFill SuperLookup from database dbFrom (with f records including fields keyFrom and valueFrom.) For simplicity assume all records are selected in both databases.
Compare ‘unWrapped’ version:
Window dbTo
Field keyTo
Formulafill superlookup("dbFrom", {keyFrom=««keyTo»»}, {valueFrom},"Wrap",false())
with ‘Wrapped’ version:
Window dbTo
Field keyTo
Formulafill superlookup("dbFrom", {keyFrom=««keyTo»»}, {valueFrom},"Wrap",true())
In unWrapped (leaving out the Wrap option would be the same; also the variations of Lookup would be similar speeds): as dbTo’s record pointer progresses from first to last record, dbFrom’s pointer resets to its first record after reaching either a matching record or its last record. On the average t*f/2 record pointer moves occur. Slow, at least by PanX standards.
In Wrapped, WITH optimized data configuration (no summary records, keyTo and keyFrom sorted in same direction, and a match for every keyTo value existing within keyFrom) dbFrom’s record pointer never returns to its first record. t+f record pointer moves occur. Much faster. BUT without the optimal data configuration dbFrom’s pointer resets to its first record often. Potentially more pointer moves than unWrapped, potentially much slower.
Ungrouping and properly sorting data to allow optimized Wrapped is trivial. Assuring 100% matches is not.
A few unmatched records may be filled via the "Default", true() option but more quickly slows the process. With "Default", false() option unmatches stop with an error, with that record active. Which can be helpful debugging when you’d expected no unmatched records. But if unmatched records are expected, the usual choice would be to employ the unWrapped version and accept the slow speed. However, it should be possible to proactively split the process, using Wrapped for the selection of known to match records and one Fill statement for the reverse known not to match. Each split faster than its unWrapped counterpart. At the cost of writing more complex code and the overhead involved in running it. So it may or not be worthwhile.
This can be done (assuming proper sorting, no summaries and all records selected):
Let matchedKeys=arrayboth(arraybuild(¶, dbFrom, {keyFrom}), arraybuild(¶, dbTo, {keyTo}), ¶)
Window dbTo
Field keyTo
Select arraycontains(matchedKeys, keyTo, ¶) ; alas, a bottleneck
Field valueTo
Formulafill superlookup("dbFrom", {keyFrom=««keyTo»»}, {valueFrom},"Wrap",true())
SelectReverse
Fill "unmatched" ; or whatever else you want as default data
SelectAll
By adding temporary placeholder fields, sorting for this then restoring a prior sort order is possible. And with more fields it could be extended to FormulaFill a partial selection of dbTo or from just a partial selection of dbFrom.
The arraybuilds are pretty quick. I believe arrayboth does its work similar to Wrap, sorting each array internally first, then just one element pointer pass for each array, as its output is sorted. It should be quick.
However, if things work as I imagine, Select arraycontains is a bottleneck. In this case matchedKeys and keyTo are sorted the same. But arraycontains doesn’t require sorted operands, can’t presume them, so its element pointer presumably resets to its first element for each new record comparison. It may take as much time as unWrapped alone. Making all this worthless.
If there is a good current PanX bypass for that bottleneck I don’t know it and would like to! If there isn’t, I request a new function, similar to arraycontains, but that doesn’t reset the element pointer for each new record comparison. Like Superlookup's Wrap option, it would require optimized data for best speed. With it, faster superlookups of partially matching records becomes practical. Not as fast as optimized Wrapped with everything matching, but still significantly faster than unWrapped.
Although I don’t know a good current PanX bypass, I do know a bad one. Append extra records to dbFrom, one for each keyTo value (placed in their keyFrom field), with your default value in their valueFrom field and with something in some other field to tag them as temporary records. Sort both databases. Then Wrapped would match all records. Then delete the temporary records. Although that kluge works you have to be desparate to employ it. My experience suggests it’s too easy to corrupt your data.