Efficient relational SELECT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Efficient relational SELECT

Simon Slavin-3
Simplified explanation.  Here's the setup:

Two tables:

rooms: each room has an id (think the standard SQLite rowid/id) and some other columns
bookings: includes room id, date, time, and some other stuff

Date/time is encoded as a long COLLATEable string.  In other words sorting a column by my date/time format will sort correctly into date order.

Bookings may be made in reverse order.  In other words, someone may book a room for next month, and after that someone may book the same room for tomorrow.

System contains about 50 rooms and 5000 bookings.  I can create whatever indexes you want.

Requirement:

I want to show an HTML table which lists some rooms (using WHERE and ORDER BY) and the latest time each of those rooms is booked for.  At the moment my code uses one SELECT to get room details and one SELECT for each room to find the most recent booking.

The obvious thing is to combine the two using JOIN.  But the best JOIN I can think of has to find the most recent booking using a clunky test for the biggest date.  And I can't figure out a good phrasing for a sub-select.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

John McKown
On Thu, Feb 4, 2016 at 12:32 PM, Simon Slavin <[hidden email]> wrote:

> Simplified explanation.  Here's the setup:
>
> Two tables:
>
> rooms: each room has an id (think the standard SQLite rowid/id) and some
> other columns
> bookings: includes room id, date, time, and some other stuff
>
> Date/time is encoded as a long COLLATEable string.  In other words sorting
> a column by my date/time format will sort correctly into date order.
>
> Bookings may be made in reverse order.  In other words, someone may book a
> room for next month, and after that someone may book the same room for
> tomorrow.
>
> System contains about 50 rooms and 5000 bookings.  I can create whatever
> indexes you want.
>
> Requirement:
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER
> BY) and the latest time each of those rooms is booked for.  At the moment
> my code uses one SELECT to get room details and one SELECT for each room to
> find the most recent booking.
>
> The obvious thing is to combine the two using JOIN.  But the best JOIN I
> can think of has to find the most recent booking using a clunky test for
> the biggest date.  And I can't figure out a good phrasing for a sub-select.
>
> Simon.
>

​CTE to the rescue!

WITH
   SELECT ID, MAX(DateTime_Column) as LastDateTime FROM bookings GROUP BY
ID​
AS room_last_used
SELECT rooms.ID, rooms.othercolumns, room_last_used.LastDateTime
FROM rooms
JOIN room_last_used
ON rooms.ID = room_last_used.ID
ORDER BY ...
;

Note - I'm old. I have arthritis. My typing sometimes stinks (like my
feet). And I can't test the above because I ain't got no data.


--
Werner Heisenberg is driving down the autobahn. A police officer pulls
him over. The officer says, "Excuse me, sir, do you know how fast you
were going?"
"No," replies Dr. Heisenberg, "but I know where I am."

Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

He's about as useful as a wax frying pan.

Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

Jim Callahan
In reply to this post by Simon Slavin-3
Might want to split  bookings into a transaction table and a reservation
table.

The reservation table would have one column for each room and one row for
each calendar day (assuming this is a respectable joint with no hourly
reservations!).

Reservation table has primary key of date and room entries start out as NA.
As rooms are booked NAs are replaced with IDs from booking-transaction
table.

Jim
<https://www.avast.com/sig-email> This email has been sent from a
virus-free computer protected by Avast.
www.avast.com <https://www.avast.com/sig-email>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Thu, Feb 4, 2016 at 1:32 PM, Simon Slavin <[hidden email]> wrote:

> Simplified explanation.  Here's the setup:
>
> Two tables:
>
> rooms: each room has an id (think the standard SQLite rowid/id) and some
> other columns
> bookings: includes room id, date, time, and some other stuff
>
> Date/time is encoded as a long COLLATEable string.  In other words sorting
> a column by my date/time format will sort correctly into date order.
>
> Bookings may be made in reverse order.  In other words, someone may book a
> room for next month, and after that someone may book the same room for
> tomorrow.
>
> System contains about 50 rooms and 5000 bookings.  I can create whatever
> indexes you want.
>
> Requirement:
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER
> BY) and the latest time each of those rooms is booked for.  At the moment
> my code uses one SELECT to get room details and one SELECT for each room to
> find the most recent booking.
>
> The obvious thing is to combine the two using JOIN.  But the best JOIN I
> can think of has to find the most recent booking using a clunky test for
> the biggest date.  And I can't figure out a good phrasing for a sub-select.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

Luuk
In reply to this post by Simon Slavin-3


On 04-02-16 19:32, Simon Slavin wrote:

> Simplified explanation.  Here's the setup:
>
> Two tables:
>
> rooms: each room has an id (think the standard SQLite rowid/id) and some other columns
> bookings: includes room id, date, time, and some other stuff
>
> Date/time is encoded as a long COLLATEable string.  In other words sorting a column by my date/time format will sort correctly into date order.
>
> Bookings may be made in reverse order.  In other words, someone may book a room for next month, and after that someone may book the same room for tomorrow.
>
> System contains about 50 rooms and 5000 bookings.  I can create whatever indexes you want.
>
> Requirement:
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER BY) and the latest time each of those rooms is booked for.  At the moment my code uses one SELECT to get room details and one SELECT for each room to find the most recent booking.
>
> The obvious thing is to combine the two using JOIN.  But the best JOIN I can think of has to find the most recent booking using a clunky test for the biggest date.  And I can't figure out a good phrasing for a sub-select.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Will this simple solution be too slow?:

SELECT id, room, date, time
FROM rooms
LEFT JOIN bookngs ON rooms.id=bookings.room
GROUP BY rooms.id, bookings.date
HAVING bookings.date=MAX(bookings.date) OR bookings.date IS NULL


<https://www.avast.com/sig-email>  This email has been sent from a
virus-free computer not protected by Avast.
www.avast.com  <https://www.avast.com/sig-email>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

Richard Hipp-3
In reply to this post by Simon Slavin-3
On 2/4/16, Simon Slavin <[hidden email]> wrote:

>
> rooms: each room has an id (think the standard SQLite rowid/id) and some
> other columns
> bookings: includes room id, date, time, and some other stuff
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER
> BY) and the latest time each of those rooms is booked for.  At the moment my
> code uses one SELECT to get room details and one SELECT for each room to
> find the most recent booking.
>

SELECT *,
    (SELECT max(booking_time) FROM bookings
      WHERE bookings.roomid=room.roomid)
   FROM room
 WHERE ....;

I think the index you want is:  "CREATE INDEX ex1 ON
bookings(roomid,booking_time);"

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

Simon Slavin-3
In reply to this post by Luuk

On 4 Feb 2016, at 7:16pm, Luuk <[hidden email]> wrote:

> Will this simple solution be too slow?:
>
> SELECT id, room, date, time
> FROM rooms
> LEFT JOIN bookngs ON rooms.id=bookings.room
> GROUP BY rooms.id, bookings.date
> HAVING bookings.date=MAX(bookings.date) OR bookings.date IS NULL

That's the solution (a phrasing of it) that I thought of.  However, in my head it's inefficient because of the test for MAX.  I was hoping for something that looked better.  But it is plenty fast enough for my purposes so any objection is purely for elegance and not for logic.

Thanks to you, John McKown and Dr Hipp.  I'll have a field day testing these out tomorrow.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

Yannick Duchêne
On Thu, 4 Feb 2016 20:40:56 +0000
Simon Slavin <[hidden email]> wrote:

>
> On 4 Feb 2016, at 7:16pm, Luuk <[hidden email]> wrote:
>
> > Will this simple solution be too slow?:
> >
> > SELECT id, room, date, time
> > FROM rooms
> > LEFT JOIN bookngs ON rooms.id=bookings.room
> > GROUP BY rooms.id, bookings.date
> > HAVING bookings.date=MAX(bookings.date) OR bookings.date IS NULL
>
> That's the solution (a phrasing of it) that I thought of.  However, in my head it's inefficient because of the test for MAX.  I was hoping for something that looked better.  But it is plenty fast enough for my purposes so any objection is purely for elegance and not for logic.
>
> Thanks to you, John McKown and Dr Hipp.  I'll have a field day testing these out tomorrow.
>
> Simon.

So what about a trigger storing the last booking date/time in a dedicated table?

--
Yannick Duchêne
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

Jake
In reply to this post by Simon Slavin-3
Hi Simon,

I do this type of query all the time to avoid sub queries and aggregation.
This might be what you are looking for to satisfy the elegance criteria:

SELECT r.room_id,
       b.date
  FROM room    r
  LEFT JOIN
       booking b  ON r.room_id = b.room_id
  LEFT JOIN
       booking b2 ON r.room_id = b2.room_id
                 AND b2.date > b.date
 WHERE b2.booking_id IS NULL

I leave it to you to understand why this works.

Regards

Jake
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficient relational SELECT

Simon Slavin-3
In reply to this post by Simon Slavin-3
All suggestions (with a bug-fix or two) gave the same results and ran in acceptable time.  Thanks to everyone for their help.  I went with the sub-SELECT solution.  Not because it gave superior results but because I'm not very familiar with sub-SELECT and it's good to have a working example in my code I can copy when I need it elsewhere.

Once again this list came thing.  Thanks.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users