Performance Analysis of Database Systems

1
Performance Analysis of Database Systems

• Akhtar Ali
• 21st May 2004

2
Motivations

• There is a current interest in comparing and
  contrasting different database
• Systems
• Data Models and
• Design considerations
• Several benchmarks have been developed
• OO7, TPC, BUCKY
• Support for systems comparison rather
  models/design
• In order to choose which database system to use,
  we need to be aware of (beside other issues)
  performance based comparison across the board.
• Relational vs. Object-Relational vs.
  Object-Oriented

3
Our Approach

• Comparison based on different data models and
  design alternatives rather than system A vs. B
• Database systems often have different
  configurations, algorithms, buffering techniques,
  block size etc
• Comparing system A against B involves several
  variables
• Difficult to isolate the impact of how the
  database is structured
• Using uniform experimental environment
• Using Oracle 9i for relational vs.
  object-relational
• Using Lambda-DB for relational vs.
  object-oriented
• Object-relational vs. object-oriented (not yet)

4
Relational vs. Object-Relational DBs

• Used the OO7 benchmark
• Implemented the OO7 database schema using Oracle
  9i
• Relational SQL (tables)
• Object-Relational SQL (object types, object
  tables, references etc)
• Implemented queries/traversals from the OO7
  benchmark on both DBs.
• Compared and contrasted elapsed time of executing
  queries/traversals against both DBs.
• Results are consistent with BUCKY ACM SIGMOD
  1997, which was implemented over Informix
  Universal Server.
• But against our expectations
• Relational database outperforms its
  object-relational counterpart.

5
OO7 Benchmark Schema
6
Results (I)

• Traversals
• T1 Traverse the assembly hierarchy. As each base
  assembly is visited, visit each of its referenced
  unshared composite parts. As each composite part
  is visited, perform a depth first search on its
  graph of atomic parts. Return a count of the
  number of atomic parts visited when done.
• Read-only traversal (T1, T6) T6 is a variant of
  T1.
• Simple update traversal (T2A, T2B, T2C)
• 1 or 20 or 4 x 20 objects are updates per
  iteration
• Index update traversal (T3A, T3B, T3C)
• Queries
• Single level query (Q5) Base Assemblies
• Ad-hoc join query (Q8) Documents and Atomic Parts

7
Results (I)
8
Relational vs. Object-Oriented DBs

• Based on the assumption
• An Object DBMS can implement a relational design
  (analogous to writing C style programmes using
  C compiler)
• Implemented the OO7 database schema using
  Lambda-DB, an OODBMS
• Realizing a relational design using ODL and OQL
• Mapping the UML schema directly using ODL and OQL
• Implemented few traversals from the OO7 benchmark
  on both DBs.
• Compared and contrasted elapsed time of executing
  traversals against both DBs.
• Results are according to our expectations
• OO outperforms Relational by 45 to 3 times.

9
Results (II)

• To our astonishment T1 took 15 days, 15 hours
  and 34 minutes to complete over the relational
  database
• Of course we did not run T1 for 15 days.
• Actually we aborted the program several times and
  executed it for a smaller dataset.
• Then estimated the elapsed time for the entire
  dataset.
• We realized that indexes were not created on
  foreign keys
• The same query took about 10 minutes.
• The re-confirms the importance of basic query
  optimization principles.

10
Results (II)
11
Conclusions

• Relational vs. Object-Relational
• RDBs have overcome some of the performance issues
  by clever optimization techniques.
• ORDBs are predominantly relying on the relational
  core.
• For example, Oracle implements nested tables with
  explicit storage outside the containing objects.
• Requires expensive unnesting and pointer
  de-referencing.
• ORDBs may need to stand on their own footing
  rather than on their predecessors - RDBs.

• Relational vs. Object-Oriented
• Use of indexes on foreign keys makes a big
  difference in RDBs.
• One of the main reason behind OODBs performance
  is that relationships are persistent.
• Objects are stored as if they are in an
  object-network rather than scattering them in
  several tables.
• RDBs have to re-construct the object-network in
  order to support the applications demands.