Re: Performance of complicated query

Sorry for the messy query, I'm very new to writing these complex queries.
I'll try and make it easier to read by using WITH clauses. However, just
to clarify, the WITH clauses only increase readability and not performance
in any way, right?

On Thu, May 23, 2013 at 4:22 PM, james <james(at)mansionfamily(dot)plus(dot)com> wrote:

> 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. ;-)
>
>