Using an ORM or plain SQL? [closed]

by

Speaking as someone who spent quite a bit of time working with JPA (Java Persistence API, basically the standardized ORM API for Java/J2EE/EJB), which includes Hibernate, EclipseLink, Toplink, OpenJPA and others, I’ll share some of my observations.

  1. ORMs are not fast. They can be adequate and most of the time adequate is OK but in a high-volume low-latency environment they’re a no-no;
  2. In general purpose programming languages like Java and C# you need an awful lot of magic to make them work (eg load-time weaving in Java, instrumentation, etc);
  3. When using an ORM, rather than getting further from SQL (which seems to be the intent), you’ll be amazed how much time you spend tweaking XML and/or annotations/attributes to get your ORM to generate performant SQL;
  4. For complex queries, there really is no substitute. Like in JPA there are some queries that simply aren’t possible that are in raw SQL and when you have to use raw SQL in JPA it’s not pretty (C#/.Net at least has dynamic types–var–which is a lot nicer than an Object array);
  5. There are an awful lot of “gotchas” when using ORMs. This includes unintended or unexpected behavior, the fact that you have to build in the capability to do SQL updates to your database (by using refresh() in JPA or similar methods because JPA by default caches everything so it won’t catch a direct database update–running direct SQL updates is a common production support activity);
  6. The object-relational mismatch is always going to cause problems. With any such problem there is a tradeoff between complexity and completeness of the abstraction. At times I felt JPA went too far and hit a real law of diminishing returns where the complexity hit wasn’t justified by the abstraction.

There’s another problem which takes a bit more explanation.

The traditional model for a Web application is to have a persistence layer and a presentation layer (possibly with a services or other layers in between but these are the important two for this discussion). ORMs force a rigid view from your persistence layer up to the presentation layer (ie your entities).

One of the criticisms of more raw SQL methods is that you end up with all these VOs (value objects) or DTOs (data transfer objects) that are used by simply one query. This is touted as an advantage of ORMs because you get rid of that.

Thing is those problems don’t go away with ORMs, they simply move up to the presentation layer. Instead of creating VOs/DTOs for queries, you create custom presentation objects, typically one for every view. How is this better? IMHO it isn’t.

I’ve written about this in ORM or SQL: Are we there yet?.

My persistence technology of choice (in Java) these days is ibatis. It’s a pretty thin wrapper around SQL that does 90%+ of what JPA can do (it can even do lazy-loading of relationships although its not well-documented) but with far less overhead (in terms of complexity and actual code).

This came up last year in a GWT application I was writing. Lots of translation from EclipseLink to presentation objects in the service implementation. If we were using ibatis it would’ve been far simpler to create the appropriate objects with ibatis and then pass them all the way up and down the stack. Some purists might argue this is Bad™. Maybe so (in theory) but I tell you what: it would’ve led to simpler code, a simpler stack and more productivity.

More Related Contents:

  1. JPQL IN clause: Java-Arrays (or Lists, Sets…)?
  2. Why are relational set-based queries better than cursors?
  3. What are the principles behind, and benefits of, the “party model”?
  4. Name database design notation you prefer and why?
  5. Best way to model Customer Address
  6. How to select a record and update it, with a single queryset in Django?
  7. Get Parent with multiple children in desc order
  8. How can I schedule a job to run a SQL query daily?
  9. Convert Month Number to Month Name Function in SQL
  10. How do I do top 1 in Oracle? [duplicate]
  11. Multiple INSERT statements vs. single INSERT with multiple VALUES
  12. Generate Dates between date ranges
  13. How to print VARCHAR(MAX) using Print Statement?
  14. Default row order in SELECT query – SQL Server 2008 vs SQL 2012
  15. What does =* mean?
  16. Google spreadsheet “=QUERY” join() equivalent function?
  17. PostgreSQL ORDER BY issue – natural sort
  18. Favourite performance tuning tricks [closed]
  19. Get month name from date in Oracle
  20. Query a database based on result of query from another database
  21. How do I import .sql files into SQLite 3?
  22. How to delete the top 1000 rows from a table using Sql Server 2008?
  23. SQL Error: ORA-00942 table or view does not exist
  24. CHECK CONSTRAINT on multiple columns
  25. What are the use cases for selecting CHAR over VARCHAR in SQL?
  26. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  27. How to extract week number in sql
  28. Why do multiple-table joins produce duplicate rows?
  29. How do I format date and time on ssrs report?
  30. How to write a WHERE Clause to find all records in a specific month?

Leave a Comment