Comment 5 for bug 1738488

Mike, we're on the same track. Here is a lightly tested branch which attempts to reduce the joins:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/collab/dbwells/lp1738488_optimize_flattener_joins

working/collab/dbwells/lp1738488_optimize_flattener_joins

More details from the commit message:

The current Flattener.pm autogenerates necessary joins for sorting and filtering, but in doing so, it gives every intermediate table a unique alias, even if the path to that table is exactly the same as another member in the map we are flattening.

Instead, let's reuse joins whenever the path is identical, even for intermediate tables. We do so by tracking every path to each core type, then reusing as much of that join path as we can. In cases where we have different paths to the same type, we still necessarily provide a new unique alias.

This problem was first noticed in the web staff billing history interface, where the particular stacking of joins resulted (for one specific library) in 17 joins and 44,575,740,147,225,592,344,870,912 potential rows.