Not a SQL replacement
Ebean ORM queries are good for typical OLTP query use cases but there are many queries that can be written in SQL that ORM is not well suited to, especially in the reporting and OLAP area. Recursive SQL queries are also not currently well supported.
Ebean ORM queries are focused to OLTP. Don't forget about using SQL where appropriate.
For Ebean the focus is to use ORM queries for OLTP use cases. The plan is not to expand the ORM
query language but to instead have good integration with SQL via
and upcoming/desired integration into
Object graph construction
It can be useful to think of ORM queries as Object graph construction. That is, when you are creating an ORM Query it is a definition on what part of the object graph to load and what filters/predicates need to be applied.
For Ebean the
fetch() control what part of the object graph to
select() is for the root level of the object graph and fetch() is for the
object graph leaves. In more advanced cases
FetchConfig can be used to control what
parts of the graph are loaded eagerly/lazily and if that part of the graph is loaded from the
database or L2 cache or L3 cache/document store.
Some parts of the object graph can be loaded from L2 cache or L3/document store
Loading - Query tuning
Tuning an ORM query for optimal performance comes down to:
- What properties to load (only load what you need for the given use case)
- What parts of the object graph to load Eagerly or Lazily
- What parts of the object graph to load from Database or L2 cache or L3 document store
- Not executing the query at all and using L2 Query cache
- Not executing the query at all and using L3 document store
Loading - AutoTune
Ebean's AutoTune feature has the ability to profile what parts of the object graph are used by the
application and then provide automatic tuning of the "what to load" part of an ORM query. That is,
AutoTune can suggest what the
fetch() part of the ORM query should
be in order to fetch only what is needed from the database (reducing network and databases costs of the query)
and minimise lazy loading.
Ebean can automatically tune "What to load"
where() clause is where you define predicates for the query. This is
effectively 'application logic' and does not change for 'tuning' purposes etc.
Predicates - Type safety
The Type Safe query extension for Ebean is designed to provide a type safe way of building query predicates. As such it does not have a focus on providing type safety on "What to fetch" via the select() and fetch() clauses (which are instead expected to be largely defined by AutoTune).
Ebean's query language is designed such that for a query you do not explicitly specify joins but instead define "What to load" and "Predicates". Ebean works out what joins are required to support both the load and predicate aspects of the query. Some of the SQL joins can be shared by both loading and predicates and some joins can not be, outer joins are 'inherited' down a given object path etc. Ebean works the joins and join types to add based on the cardinality and optionality of the relationships/paths involved in both loading and predicates.
The downside of this approach is that predicates using "self joins" can not be easily specified currently but this issue will be addressed and there are some very significant benefits to this approach where Ebean determines the joins automatically.
Avoiding Cartesian product
Ebean will never generate a SQL cartesian product. No matter how complex or big you ORM query gets Ebean will not generate a SQL cartesian product but instead break the query up into multiple SQL queries.
MaxRows will always work in your ORM query. That is, firstRows/maxRows work on relational
rows (and not objects) and Ebean will automatically break the ORM query up into multiple SQL queries as needed in
order for firstRows/maxRows to work as intended.
Arbitrarily complex graphs
No matter how complex or nested your object graph is for your ORM query Ebean can build it in a pretty efficient manor. More specifically, a single SQL query can contain at most one OneToMany or ManyToMany relationship "path" (without producing a SQL cartesian product which is what we really want to avoid). Ebean will break up the ORM query into multiple SQL queries with each SQL query having a single OneToMany/ManyToMany relationship in its fetch (each SQL query is as eager/big as it can be without becoming a cartesian product).
Ebean processes all the fetch paths determining which paths contain a OneToMany or ManyToMany relationship.
The query 'breakup' may be manually controlled by using
FetchConfig but if this has not been done
or has not covered all the required relationships then Ebean will automatically break up the query based
on these 'paths containing a many relationship'.
The "Load Context" is an internal Ebean feature with a very important job. Is supports executing secondary queries and provides:
- Batch lazy loading
- Query joins (Eager secondary queries)
- Propagation of key query state from the origin query to any/all secondary queries.
In essence the Load Context provides the mechanism for batch loading complex graphs.
"Origin query" is an Ebean term to refer to the original SQL query executed when building an object graph. Further SQL "secondary queries" can execute and relate back to the "origin query".
In summary logging there is an
origin attribute and this is a key that can be used to link
origin and secondary queries.
txn FindBean type[Order] origin[B0dP9E.DWeHD4.5WUnB] ...
"Secondary queries" is an Ebean term for SQL queries that relate back to a previously executed "origin query". These secondary queries can be executed due to lazy loading or because a single ORM query is broken up manually or automatically into multiple SQL queries (in order to avoid cartesian product and support firstRows/maxRows).
In summary logging the
origin attributes can be used to identify which sql
queries are "secondary queries" and the origin which they relate back to. This logging can be used to identify
excessive lazy loading (N + 1) for example.
mode: +lazy for lazy loading query and +query for a query join/eager loading
origin: this key links the secondary query back to the origin
txn FindBean type[Order] origin[B0dP9E.DWeHD4.5WUnB] ... ... txn FindMany mode[+lazy] type[Customer] origin[B0dP9E.DWeHD4.5WUnB] ... ... txn FindMany mode[+lazy] type[Address] origin[B0dP9E.DWeHD4.5WUnB] ...