[OPEN-ILS-DEV] Localized grace days

[James Fournie]

Hi there,

I am attempting to add a localized org unit setting which allows for
different grace periods per org unit.  I am wondering if anyone could
consider my strategy if it seems reasonable.

I have started by isolating the part of the fine generator that pulls
in all the overdue circs.  This is
OpenILS/Storage/Publisher/action.pm, the overdue_circs method.

The current SQL is something like this, where x is the grace period
passed into the fine generator method:

SELECT  *
    FROM action.circulation
    WHERE stop_fines IS NULL
    AND due_date < ( CURRENT_TIMESTAMP - (x * (fine_interval)))
    AND fine_interval < '1 millennium'::INTERVAL;

I think this could be changed to something like this:

SELECT *
    FROM  action.circulation
        WHERE stop_fines IS NULL
    AND due_date <
    ( CURRENT_TIMESTAMP - (coalesce((select value
        FROM actor.org_unit_ancestor_setting('circ.grace_days', circ_lib)
        limit 1)::integer,0) * (fine_interval)))
    AND fine_interval < '1 millennium'::INTERVAL;

However, this is a fairly expensive query that clocks in at around 18
seconds vs the original query's 3 seconds.  I am not sure if this is a
worthwhile difference, but considering that the fine generator does
not typically run very often, it may not be a problem.  I'm wondering
how serious this is, or what problems this could potentially cause.
It could have an impact if the fine generator is run very frequently
(say hourly).

I'm stumped as to a different way to approach this problem.  The fine
generator currently runs on all overdue circs, and so basically any
kind of separating this out by org unit is going to result in a
performance hit.  I did try add a method to replace the cumbersome
coalesce() and ancestor_setting call in the middle there, but it
didn't really result in a drastic improvement, only a couple of
seconds.

Thanks

~James