On Tue, Dec 27, 2016 at 10:52:51PM +0100, Henrik Sarvell wrote: > ## We want all projects that are NOT outside of the range, ie: > ## 1.) End date is lower than the start date of the range. > ## 2.) Start date is higher than the end date of the range. > ## Therefore we generate all projects whose: > ## 1.) Start date is smaller than the end date of the range. > ## 2.) End date is larger than the start date of the range. > ... > The above works, tested 100%, it looks like it's going to scan the whole > database though in the generate clauses, which means collect on id with > subsequent list filter is probably just as fast, or perhaps not?
Right. And that is the problem! You always end up scanning the whole DB. You could try to improve the situation somewhat by assuming a maximal project length, so that you don't need to scan from -infinite till +infinite. But this is not really satisfactory. That's why I think there is no way around +UB trees. They give a single index to traverse, so you avoid having to AND two separate open-ended ranges. You get the exact hits directly from the scan / collect. As I said, the start date S and the end date E are coordinates in a 2-dimensional space. A project is a point in that space: ^ S | / | / | / | / | / | / | / | / | / | / | / |/ --------------+--------------> E /| / | / | / | / | / | X / | / | / | / | / | / | If we assume that E >= S, then all legal projects will be right/below the diagonal line (today is S = 0 and E = 0). All projects which start today or earlier, AND end today or later (i.e. the ones you want to collect) must lie in quadrant X. So to collect all projects which have sDate <= today AND eDate >= today (let Today (date) (collect 'sDate '+Proj (list Today Today) (list NIL T)) ) Won't that work? - Alex -- UNSUBSCRIBE: mailto:email@example.com?subject=Unsubscribe