Re: Performance of complicated query

1. Reads is constantly inserted upon. It should never be updated or
deleted.
2. I suppose I can, but that will make my insertion logic very
complicated. I cannot guarantee the order of any of this data, so I might
get reads at any time and also get assignments at any time (historical as
well). I suppose I could do that, but I'd like to avoid it if at all
possible.
3. 2 measurements can have the same date, and that is fine. The problem
arises when the same device produces 2 reads at the same time and that
isn't possible.
4. I agree that a lot of this is a mess, however MAX(max_read)
OVER(PARTITION BY patient_id) AS latest_read seems necessary as using a
group by clause forces me to group by all elements I'm selecting, which I
don't want to do.

On Thu, May 23, 2013 at 12:23 PM, Vladimir Sitnikov <
sitnikov(dot)vladimir(at)gmail(dot)com> wrote:

> >>This leads to the WHERE clause, WHERE read_datetime = max_read, and
> hence I'm only summing the last read for each device for each patient.
> Is "reads" table insert-only? Do you have updates/deletes of the
> "historical" rows?
>
> >>3. Can I modify my tables to make this query (which is the crux of my
> application) run faster?
> Can you have a second "reads" table that stores only up to date values?
> That will eliminate max-over completely, enable efficient usage in other
> queries, and make your queries much easier to understand by humans and
> computers.
>
> PS. read_datetime = max_read is prone to "what if two measurements have
> same date" errors.
> PPS. distinct MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read
> looks like a complete mess. Why don't you just use group by?
>
>>
> Regards,
> Vladimir
>