On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote:
> How about the direct approach:
>
> SELECT uid
> FROM resource
> WHERE uid NOT IN (SELECT resource_uid
> FROM event_participant, event
> WHERE event_participant.event_uid = event.uid
> AND event.shift_uid = :shift_uid
> AND event.date = :event_date)
>
> or perhaps
>
> SELECT uid
> FROM resource
> WHERE NOT EXISTS (SELECT 1
> FROM event_participant, event
> WHERE event_participant.event_uid = event.uid
> AND resource_uid = resource.uid
> AND event.shift_uid = :shift_uid
> AND event.date = :event_date)
>
> Is not the "right way to do it" the one that obtains the result required, not
> the one that uses a "checkbox" implementation?
I've presented 2 queries which both obtain the required result, and I
would have to say both of them have varying levels of correctness.
If obtaining the correct result was all that was required, then I could
simply issue multiple queries and balloon my memory with result UIDs
in between each of them, all for the sake of better readability (but
then, I'm not sure why I would need a nice tool like SQLite to do it
in the first place).
So I would have to say, the "right way to do it" is the most efficient
way, the one which provides SQLite with the best indications of how
to plot an efficient query plan.
The heuristics of the table is generally that resources are rather
finite, while events grow over time (possibly 10 events each with
10 participants for 3 shifts per day, meaning after a year that
table will be large, and we'll still want to find the available
resources hopefully in under 30ms, ideally 10ms).
So yeah, speed at query time (not insert/update/delete time), is very
important.
Best Regards,
-Tristan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
