Performance Tuning on Hotel Reservations

1. Performance Tuning on HotelReservations Sanitized consulting story

2. Schema 1 • Roomdate(hotelid, roomtypeid, date, language, description) • Translation(description1, language1, description2, language2) e.g.( “double-room with breakfast”, english, “doppel-zimmer halb pension”, german) • Any comments so far?

3. Procedure 1 • To insert new information about a hotel for a date range, set up a loop in informix over roomtype and then over date that calls a translation subroutine for every roomtype-date combination. Translation looks up information in the translation table. • Any comments so far?

4. Alternative • Description(descid, language, description) • Roomdate(hotelid, roomtypeid, date, descid) • Language can be obtained when needed. • Inserting can be done without looping and without any lookups. • Queries for a particular date roomtype and hotel require a join with Description.

5. New Problem: pricing • Input: a date range, an occupancy specification (e.g. two adults one child), a hotel, a room type, and other attributes (e.g. half pension, sea view, high speed internet) • Output: a price per night • Can write a program for one hotel, but for 100,000?

6. Goal • Single framework for all hotels • Table-driven so hotel managers can enter their specifications without much training or typing. • Ability to identify inconsistencies and incompleteness. • Allow sophisticated queries: price shopping, feature shopping.

7. Representation • Represent a room price-distinguishable condition by attribute-value pairs. • Hotel decides which attributes apply. • Ex: hotel has single and double rooms and charges more for a cot but only in double rooms. • (roomtype: single), (roomtype: double), (roomtype: double, cot: yes)

8. Processing • Most specific wins. • If a customer specifies a single room with a sea view, there is no extra charge for the view since the most specific single room spec has no dependency on view. • On the other hand double room with cot would get that row’s price.

9. Inconsistency • Get inconsistency if cannot identify a unique most specific. • Ex: (roomtype: double room; cot: yes)(roomtype: double room; view: sea). • What happens if one wants a double room with a cot and a sea view? (Disambiguate based on lower price?)

10. Detecting Inconsistency • Advantage of table representation is that detection of inconsistency is hotel independent and requires no analysis of code. Detected at time of spec. • Ex: (roomtype: double room; cot: yes)(roomtype: double room; view: sea). • Detection by noticing neither spec is subset of other.

11. Detecting Incompleteness • Hotel specifies mandatory attributes. • For each combination of mandatory attribute values expect condition for that. • So, all mandatory and no voluntary is possible. • In our example, if roomtype is the only mandatory attribute then there should be a row having only double room.

12. Dates • Dates are involved since discounts may require staying entirely within a date range, partly within a date range, over a certain date etc. • Would be nice to be able to specify these separately and then combine with other attributes as opposed to a big table. • Still open.

13. Queries easily possible • Customer chooses a city: any double rooms with a sea view under a certain price? • What is cheapest for this hotel assuming I need a cot? • What is cheapest for this date range?

14. Suggested Implementation • Boolean vector to indicate included/deleted. • Indexes on attribute names, on hotels, cluster based on hotel and condition. • Every selection would return a list of hotel-condition pairs. These should be intersected. Can use either bit vectors or a dynamic hash structure. Dynamic hash usually better.

15. Summary and Next Steps • Table-driven specs can be easier for the user, simpler to implement, and easier to verify. • How would one make such a thing very efficient?