Re: Performance of complicated query

On 23/05/2013 22:57, Jonathan Morra wrote:
> I'm not sure I understand your proposed solution. There is also the
> case to consider where the same patient can be assigned the same
> device multiple times. In this case, the value may be reset at each
> assignment (hence the line value - issued_value AS value from the
> original query).
>

Perhaps you could use triggers to help somewhat? At least for the
lifetime part.

For a given assignment of a device to a patient, only the last value is
useful, so you can maintain that easily enough (a bit like a
materialised view but before 9.3 I guess).

But, that might fix 'lifetime' but not some arbitrary windowed view. I
can see why an 'as at' end time is useful, but not why a start time is
so useful: if a device has readings before the window but not in the
window, is that 'no reading' or should the last reading prior to the
window apply?

It also seems to me that the solution you have is hard to reason about.
Its like a Haskell program done in one big inline fold rather than a
bunch of 'where' clauses, and I find these cause significant brain overload.

Perhaps you could break it out into identifiable chunks that work out
(both for lifetime if not using triggers, and for your date range
otherwise) the readings that are not superceded (ie the last in the date
bounds for a device assignment), and then work with those. Consider the
CTE 'WITH queries' for doing this?

It seems to me that if you can do this, then the problem might be easier
to express.

Failing that, I'd be looking at using temporary tables, and forcing a
series of reduce steps using them, but then I'm a nasty old Sybase
hacker at heart. ;-)