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 rows 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?