Object Relational Mappers: Friend or Foe

1
Object Relational Mappers Friend or Foe?

• Glenn Paulley, Director, Engineering
• Sybase iAnywhere
• http//iablog.sybase.com/paulley

2
What is object-relational mapping?

• Object-oriented programming technologies are
  typically used to implement business applications
  today
• Relational databases are the predominant
  persistent data storage solution
• Impedance mismatch between the two paradigms
  objects vs. relations
• Estimated that 30-40 of a JDBC application
  involves coercing data from tuples to object
  instances and back again
• ORM toolkits are designed to address this
  impedance mismatch
• 61 different ORM toolkits are listed in Wikipedia
  for Java, C, Delphi, Ruby, .NET, PHP, Python,
  Perl

3
The promise Part 1

• Developers spend far too much time worrying about
  their backend database, its tables and their
  relationships, the names and parameters of stored
  procedures and views, as well as the schema of
  the data that they return. Microsofts new Entity
  Framework changes the game for .NET developers so
  that we no longer have to be concerned with the
  details of the data store as we write our
  applications. We can focus on the task of writing
  our applications, rather than accessing the data.
• -- Julie Lerman, Programming Entity Framework,
  pp. 1

4
What is object-relational mapping?

• To exploit object behaviour fully, data-access
  from within an object-oriented programming
  language should offer
• Separation of concerns
• Information hiding
• Inheritance
• Change detection
• Uniqueness capability
• Database independence

5
Change detection

• ORM toolkits require mechanisms to track changes
  to objects made by the application
• When a transaction is complete, write the changes
  to the database within an atomic transaction
• Need appropriate guarantees to prevent lost
  updates either
• Pessimistic locking using the database servers
  locking implementation
• Optimistic locking using timestamps or version
  numbers, handled by the ORM layer but supported
  by the underlying relational schema

6
Uniqueness

• Mappings are usually a correspondence between a
  row in a normalized table and a class instance
• Specified using metadata Hibernate (NHibernate)
  and Entity Framework utilize XML
• For example, a row of the Employee table will
  correspond to an instance of the Employee object
  within the application
• Mappings are often not isomorphic
• Sophisticated ORMs such as Hibernate and LINQ
  permit object models that differ substantially
  from the underlying relational store
• Object-oriented language features offer greater
  semantic flexibility in application design than
  1NF values from a relational database
• Need to establish a correspondence between an
  in-memory object and a database row
• Must be independent of how the object was
  acquired a database query, or navigating a
  reference to another object
• Predicated on the existence of primary keys in
  the database

7
Database independence

• Many ORM toolkits attempt to offer database
  independence, so that applications can be ported
  from one DBMS to another
• Create common APIs and models to interact with a
  variety of DBMS platforms
• Useful with mobilized applications where the
  consolidated database is one DBMS, and local
  databases are different
• APIs tend to be modeled on SQL-92!
• Hibernates HQL
• Entity Frameworks LINQ

8
The promise Part 2

• ORM Toolkits can
• Eliminate tedious, repetitive code that
  instantiates object instances from tuples using a
  SELECT statement and a CURSOR
• Insulate, to some extent, the application
  developer from vendor-specific SQL extensions
• Permit the application developer to exploit
  object-orientation and model and manipulate the
  application view differently from the relational
  model
• Allow data manipulation to be done at the
  object level, rather than (only) at a SQL
  statement level

9
NHibernate HelloNHibernate example

• static void CreateEmployeeAndSaveToDatabase()
• Employee tobin new Employee()
• tobin.name "Tobin Harris"
• using (ISession session
  OpenSession())
• using (ITransaction transaction
  session.BeginTransaction())
• session.Save(tobin) //
  generates appropriate SQL INSERT statement
• transaction.Commit()
• Console.WriteLine("Saved Tobin to
  the database")
• Look Mom no SQL!

10
NHibernate HelloNHibernate example

• static void LoadEmployeesFromDatabase()
• using (ISession session
  OpenSession())
• IQuery query session.CreateQuery
  (
• "from Employee as emp order
  by emp.name asc")
• IListltEmployeegt foundEmployees
  query.ListltEmployeegt()
• Console.WriteLine("\n0
  employees found",
• foundEmployees.Count)
• foreach (Employee employee in
  foundEmployees)
• Console.WriteLine(employee.Say
  Hello())

11
The Challenge Part 1

• ORM toolkits introduce an additional level of
  complexity to the application
• Example Java Hibernate 3.2.6 is
• 266 packages, 1938 classes, 18,680 functions,
  over 118K LOC
• Can be difficult to debug, perform performance
  analysis
• Most frameworks suffer from a lack of appropriate
  tools
• Ayende Rahien has developed an NHibernate
  Profiler that provides sophisticated debugging
  and performance analysis for NHibernate
  applications

12
The Challenge Part 2

• Performance analysis is problematic because the
  applications behaviour is not tied directly to
  specific interactions with the database
• ORM tools obfuscate the difference between
  expensive and inexpensive database interactions
• Complex object-relational mappings may cause very
  complex SQL queries to be generated
• Generated SQL requests may not reach the database
  server in the same order due to ORM write-behind
  policies
• Can be difficult for the application developer to
  understand what caused the construction of a
  specific SQL request

13
Complex SQL (LINQ generated)

• SELECT
• Project9.ContactID AS ContactID,Project9.
  C1 AS C1,Project9.C2 AS C2,Project9.Co
  ntactID1 AS ContactID1,Project9.SalesOrderID
  AS SalesOrderID,
• Project9.TotalDue AS TotalDue
• FROM ( SELECT         Distinct1.ContactID AS
  ContactID,        1 AS C1,       
  Project8.ContactID AS ContactID1,       
  Project8.SalesOrderID AS SalesOrderID,
•         Project8.TotalDue AS
  TotalDue,        Project8.C1 AS C2
•         FROM  
• (SELECT DISTINCT   Extent1.ContactID AS
  ContactID    
•             FROM  DBA.Contact AS Extent1
•                           INNER JOIN
  DBA.SalesOrderHeader AS Extent2
• ON  EXISTS (SELECT   cast(1 as bit) AS C1
•                         FROM    ( SELECT cast(1
  as bit) AS X ) AS SingleRowTable1
•                         LEFT OUTER JOIN  (SELECT
  Extent3.ContactID AS ContactID
•                                 FROM
  DBA.Contact AS Extent3  WHERE
  Extent2.ContactID Extent3.ContactID )AS
  Project1 ON cast(1 as bit) cast(1 as bit)
•                         LEFT OUTER JOIN  (SELECT 
  Extent4.ContactID AS ContactID
•                                 FROM
  DBA.Contact AS Extent4  WHERE
  Extent2.ContactID Extent4.ContactID )
  AS Project2 ON cast(1 as bit) cast(1 as bit)
•                         WHERE (Extent1.ContactI
  D Project1.ContactID) OR
  ((Extent1.ContactID IS NULL) AND
  (Project2.ContactID IS NULL))  )
• ) AS Distinct1
•   LEFT OUTER JOIN 
•  (SELECT  Extent5.ContactID AS ContactID, 
  Extent6.SalesOrderID AS SalesOrderID, 
  Extent6.TotalDue AS TotalDue,   1 AS C1

14
Equivalent SQL query

• select  Extent6.ContactID, 
• 1 as C1, 
• 1 as C2,
• Extent6.ContactID as ContactID1,  
• Extent6.SalesOrderID as SalesOrderID,
• Extent6.TotalDue as TotalDue
• from DBA.SalesOrderHeader as Extent6
• order by Extent6.ContactID asc
• Can your query optimizer get there?
• And what program construction caused the original
  statement to be formed in the first place?

15
The Challenge Part 3

• Abstraction is great, but.
• The application programmer now relies on the ORM
  toolkit for concurrency control and the
  generation of database requests, the two most
  important performance factors of a database
  application
• In practice, ease of development is traded-off
  for poorer run-time performance due to additional
  code complexity
• Julie Lerman 220 increase in query execution
  cost between using a DataReader directly and
  using Microsofts LINQ to Entities (pp. 503)
• Jeff Atwood (http//www.codinghorror.com/blog/arc
  hives/001281.html) A simple example shows a
  factor of two difference between using a
  DataReader and LINQ to SQL.

16
The Challenge Part 4

• Object-instance-at-a-time navigation through the
  object model can result in multiple, separate
  interactions to the database server to retrieve
  the rows to create the objects
• Termed the N1 Selects problem
• Application developer must tradeoff prefetching
  applicability in various scenarios these are
  documented in the various toolkits
• Objects are not partially instantiated all
  object attributes are required for the
  constructor
• Transactional semantics are complex once caching
  is introduced
• Transactional semantics often differ across
  DBMSs, even with the identical isolation level
• Developers must (still) be aware of the potential
  for lost updates

17
The Challenge Part 5

• The SQL dialect supported by ORM toolkits is
  typically a very restricted subset of ANSI SQL
• Hibernates HQL supports inner and outer join,
  simple GROUP BY and HAVING, quantified subqueries
• Workarounds are to perform the computation on the
  client (within the application), or work outside
  of the ORM toolkit using a direct database
  connection through a native API
• ORM toolkits often support only limited,
  straightforward DDL for schema creation/modificati
  on
• Not supported in current versions of Entity
  Framework
• Hibernate-generated schemas are not recommended
  for production applications
• Deployment of the application should involve
  separate DDL scripts

18
Important aspects of ORM toolkits

• Mapping specification
• Query language
• Persistence
• Class inheritance
• Fetch strategies
• Caching
• We look and see how these are implemented in
  NHibernate, a popular open-source ORM toolkit for
  C applications

19
NHibernate as an example of an ORM
20
Brief introduction to NHibernate

• Open-source, LGPL C ORM toolkit
• Forked from the original Java implementation
  called Hibernate
• Support for similar, but not identical, metadata
  XML
• Similar architecture, but significant differences
  in methods and support
• Hibernate was originally developed by Christian
  Bauer, Gavin King, and a worldwide team of
  developers
• NHibernate now maintained by another group
  worldwide
• Current distribution is 2.1 (released in July
  2009)
• http//hibernate.org

21
NHibernate application architecture
NHibernate.DLL
22
NHibernate mapping specification

• CREATE TABLE "GROUPO"."SalesOrders" (
• "ID" integer NOT
  NULL DEFAULT autoincrement
• ,"CustomerID" integer NOT
  NULL
• ,"OrderDate" date NOT
  NULL
• ,"FinancialCode" char(2) NULL
• ,"Region" char(7) NULL
• ,"SalesRepresentative" integer NOT
  NULL
• ,CONSTRAINT "SalesOrdersKey" PRIMARY KEY
  ("ID")
• )
• ALTER TABLE "GROUPO"."SalesOrders
• ADD FOREIGN KEY "FK_SalesRepresentative_Employ
  eeID" ("SalesRepresentative) REFERENCES
  "GROUPO"."Employees" ("EmployeeID")
• ALTER TABLE "GROUPO"."SalesOrders
• ADD FOREIGN KEY "FK_FinancialCode_Code"
  ("FinancialCode")
• REFERENCES "GROUPO"."FinancialCodes" ("Code)
• ON DELETE SET NULL
• ALTER TABLE "GROUPO"."SalesOrders
• ADD FOREIGN KEY "FK_CustomerID_ID"
  ("CustomerID")
• REFERENCES "GROUPO"."Customers" ("ID)

23
NHibernate mapping specification

• NHibernate mapping file SalesOrders.hbm.xml
• lt?xml version"1.0"?gt
• lt!DOCTYPE hibernate-mapping PUBLIC
  "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
• "http//hibernate.sourceforge.net/hibernate-mappin
  g-3.0.dtd"gt
• lt!-- Generated Mar 3, 2009 115955 AM by
  Hibernate Tools 3.2.2.GA --gt
• lthibernate-mappinggt
• ltclass name"SalesOrders" table"SalesOrders"gt
  
• ltcommentgtsales orders that customers have
  submitted to the sporting goods companylt/commentgt
• ltid name"id" type"int"gt
• ltcolumn name"ID" /gt
• ltgenerator class"assigned" /gt
• lt/idgt
• ltmany-to-one name"employees"
  class"Employees" fetch"select"gt
• ltcolumn name"SalesRepresentative"
  not-null"true" /gt
• lt/many-to-onegt
• ltmany-to-one name"financialCodes"
  class"FinancialCodes" fetch"select"gt
• ltcolumn name"FinancialCode"
  length"2" /gt
• lt/many-to-onegt
• ltmany-to-one name"customers"
  class"Customers" fetch"select"gt

24
C class implementation

• using System
• using System.Collections.Generic
• using NHibernate
• namespace HelloNHibernate
• class SalesOrder
• public Int32 id
• public Employees employees
• public FinancialCodes financialCodes
• public Customers customers
• public DateTime orderDate
• public String region
• public Set salesOrderItemses new
  HashSet(0)
• public SalesOrders()
• public SalesOrders(Int32 id, Employees
  employees, Customers customers, DateTime
  orderDate)

25
HQL Hibernate Query Language

• Subset of ANSI query specification with support
  for
• DISTINCT
• GROUP BY, simple aggregation
• INNER, LEFT- and RIGHT-OUTER JOIN
• Quantified subqueries
• Supports object-oriented dot-notation for
  many-to-one, one-to-one associations, for
  example

select s.id, s.orderDate, s.region,
s.customers.givenName, s.customers.surname from
SalesOrders s where s.orderDate between
'2001-03-16' and '2001-03-26' and s.region ltgt
'Central' order by s.orderDate
26
HQL Hibernate Query Language

• NHibernates HQL supports SELECT, inner and outer
  JOIN, WHERE, HAVING, simple GROUP BY, UNION,
  ORDER BY, self-joins with different correlation
  names
• HQL does not support recursion, common table
  expressions, window functions, derived tables,
  other set operators, table functions, array or
  structured types, APPLY/OUTER APPLY, CROSS JOIN,
  GROUP BY CUBE/ROLLUP/GROUPING SETS, FULL OUTER
  JOIN

select distinct c.givenName from Customers c
left join c.salesOrderses where c.city in
(select c2.city from Customers c2 where
c2.companyName like 'Power')
27
HQL Hibernate Query Language

• HQL is augmented by dialects that implement
  specific methods to modify the SQL generated by
  NHibernate before execution on the server
• HQL has optional support for (implemented by a
  specific dialect)
• UNION ALL (for entity-type hierarchies)
• LIMIT (SELECT TOP N), OFFSET
• IDENTITY, GUID, SEQUENCE data types
• Syntax to declare an updateable cursor and
  locking mode
• Case-insensitive string comparisons

28
Object persistence in NHibernate

• Saving objects
• Once an object is created or modified, it must be
  saved explicitly and then the transaction must be
  committed
• session.Save(ltobject namegt)
• tx.Commit()
• Loading objects
• The NHibernate session interface offers several
  load() methods for loading objects into memory
  from database tuples
• Cat fritz (Cat) session.Load(typeof(Cat),
  generatedId)
• Cat cat (Cat) session.Get(typeof(Cat), id,
  LockMode.Upgrade) // OR this
• session.Save(cat)
• session.Flush() //force the SQL INSERT
• session.Refresh(cat) //re-read the state (after
  the trigger executes)

29
Object persistence in NHibernate

• A refresh() method is implemented to reload
  objects from the database
• Useful for when attributes are modified by
  database triggers upon INSERT or UPDATE
• Highly error-prone
• Updating objects
• NHibernate manages changes to persistent objects
  transparently and automatically
• If an attribute is altered, the appropriate
  NHibernate session will queue the change for
  writing to the database using SQL
• One can force changes to be written at a certain
  point using the flush() method, controlled by
  isDirty() and setFlushMode()

30
Class inheritance

• Hibernate and NHibernate offer a variety of
  built-in techniques to handle different
  normalizations of entity-type hierarchies
• Single table with discriminator value
• Multiple tables fully normalized into BCNF
• A hybrid model consisting of a mixture of the two
• If represented as different objects in a mapping,
  an ETH requires careful construction and
  different equals() and hashcode() implementations

31
FETCH strategies

• A major pain point is the N1 SELECTs problem
• Navigation through the object model iteratively
  causes additional rows to be retrieved from the
  database, using independent SQL requests
• client-side join performance tends to be
  extremely poor due to the additional latency
• Need careful construction of HQL queries to
  minimize client-server latency
• Alternatively, in the mapping one may specify the
  selection method either lazy or eager
• One must tradeoff this method will global
  application behaviour, or override it on a
  case-by-case basis using HQL-specific syntax

32
Concurrency control

• NHibernate relies on the databases locking
  scheme for concurrency control
• ANSI isolation levels 0-3 are supported directly
  1 or 2 is recommended (READ COMMITTED and
  REPEATABLE READ)
• DBMS that support snapshot isolation require
  tweaks to their NHibernate dialect implementation
• Lock mode for individual HQL statements or
  instantiations of objects can be specified
  directly, ie
• Customer c (Customer) session.Get(Customer.class
  , 101, LockMode.Upgrade)
• NHibernate has builtin support for optimistic
  concurrency control
• Can use either version numbers (added to the
  schema of the table), or base change control on
  all of the values in the row

33
Caching and concurrency control

• Like many runtime environments, NHibernate
  supports built-in caching controls to speed-up
  database interaction
• Two levels of caching are supported
• Level 1 Persistence context cache
• Lifespan is a transaction or a conversation
  without sharing.
• Guarantees scope of the object and object
  identity. Mandatory.
• Level 2 Pluggable, scope is process or cluster
  (shared)
• Configurable on a class-by-class basis
• Selectable concurrency control strategies
  Transactional, Read-write, non-strict read-write,
  read-only
• ASP.NET Cache, PrevalenceCache are
  implementations

34
Summary

• Are ORM toolkits a failed abstraction?
• Similar arguments to compiler technology in the
  1970s
• There are productivity gains with such tools, but
  there are tradeoffs with additional run-time
  costs
• Jeff Atwood programmers must still understand
  the entire program stack from end to end
• Application complexity is increased
• However - for open source toolkits, documentation
  and support is what one would expect with free
  software
• Developer productivity gains are real
• Permits specialization of development teams
  database expertise is still necessary

35
Research and development opportunities

• Robust, industrial-strength debugging frameworks
• Identify how a particular database interaction
  was caused by what specific application program
  behaviour
• Analysis of concurrency control behaviour to
  determine application correctness, for example
  with respect to lost updates
• Query processing optimizations specific for ORM
  application toolkits
• Identifying common classes of SQL queries for
  specific sets of optimizations
• Place in the dialect layer, in the DBMS itself,
  or in a separate tool that can support the
  optimization of multiple inter-related statements
• Mechanisms to support additional types of
  mappings and more complex schemas
• Support for recursion in toolkit languages

36
Questions?

• Watch my blog for continuing articles on
  supporting ORM toolkits with SQL Anywhere
  http//iablog.sybase.com/paulley
• References
• Christian Bauer and Gavin King (November 2007).
  Java Persistence with Hibernate (revised edition
  of Hibernate in Action). Manning Publications,
  Greenwich, CT. ISBN 1-932394-88-5. Seventh
  printing.
• Dave Minter and Jeff Linwood (2005) Pro Hibernate
  3. Apress Books, Berkeley, CA.
• Julia Lerman ( February 2009). Programming Entity
  Framework. OReilly Media, Inc., Sebastopol,
  California. ISBN 978-0-596-52028-1.
• Roger Jennings (2009). Professional ADO.NET 3.5
  with LINQ and the Entity Framework. Wiley
  Publishing, Indianapolis, Indiana.
  978-0-470-18261-1.
• Joseph C. Rattz, Jr. (2007). Pro LINQ Language
  Integrated Query in C 2008. Apress Books,
  Berkeley, CA. 978-1-59059-789-7.
• Pierre Henri Kuate, Tobin Harris, Christian Bauer
  and Gavin King (2009). NHibernate in Action.
  Manning Publications, Greenwich, CT. ISBN
  978-1-932394-92-4.
• ACM Queue 6(3), May/June 2008, Bridging the
  Object-Relational Divide.