This is an automated email from the ASF dual-hosted git repository.
aadamchik pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/cayenne.git
The following commit(s) were added to refs/heads/master by this push:
new c326e65 prefetch semantics docs chapter reorg, clarification
c326e65 is described below
commit c326e650af2409484a7fb72774a1126cae356647
Author: Andrus Adamchik <[email protected]>
AuthorDate: Thu Jul 30 10:00:21 2020 +0300
prefetch semantics docs chapter reorg, clarification
---
.../asciidoc/_cayenne-guide/part2/queries/sql.adoc | 2 +
.../docs/asciidoc/_cayenne-guide/part2/tuning.adoc | 84 +++++++++++++---------
2 files changed, 51 insertions(+), 35 deletions(-)
diff --git
a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
index d3419bf..732c983 100644
---
a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
+++
b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
@@ -11,6 +11,8 @@
// CONDITIONS OF ANY KIND, either express or implied. See the License for
// the specific language governing permissions and limitations under the
// License.
+
+[[sqlselect]]
==== SQLSelect and SQLExec
SQL is very powerful and allows to manipulate data in ways that can not always
be described as a graph of related entities.
diff --git
a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/tuning.adoc
b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/tuning.adoc
index bbcdeb5..87f64e1 100644
---
a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/tuning.adoc
+++
b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/tuning.adoc
@@ -25,20 +25,18 @@ Prefetching example:
[source, Java]
----
-ObjectSelect<Artist> query = ObjectSelect.query(Artist.class);
-
-// instructs Cayenne to prefetch one of Artist's relationships
-query.prefetch(Artist.PAINTINGS.disjoint());
-
-// the above line is equivalent to the following:
-// query.prefetch("paintings", PrefetchTreeNode.DISJOINT_PREFETCH_SEMANTICS);
+ObjectSelect<Artist> query = ObjectSelect
+ .query(Artist.class)
+ // Instructs Cayenne to prefetch one of Artist's relationships.
+ // Equivalent to ".prefetch("paintings",
PrefetchTreeNode.DISJOINT_PREFETCH_SEMANTICS)")
+ .prefetch(Artist.PAINTINGS.disjoint());
// query is expecuted as usual, but the resulting Artists will have
// their paintings "inflated"
List<Artist> artists = query.select(context);
----
-All types of relationships can be preftetched - to-one, to-many, flattened. A
prefetch can span multiple relationships:
+All types of relationships can be prefetched - to-one, to-many, flattened. A
prefetch can span multiple relationships:
[source, Java]
----
@@ -49,18 +47,19 @@ A query can have multiple prefetches:
[source, Java]
----
-query.prefetch(Artist.PAINTINGS.disjoint());
-query.prefetch(Artist.PAINTINGS.dot(Painting.GALLERY).disjoint());
+query.prefetch(Artist.PAINTINGS.disjoint())
+ .prefetch(Artist.PAINTINGS.dot(Painting.GALLERY).disjoint());
----
If a query is fetching DataRows, all "disjoint" prefetches are ignored, only
"joint" prefetches are executed
(see prefetching semantics discussion below for what disjoint and joint
prefetches mean).
-===== Prefetching Semantics
+===== Prefetch Semantics
-Prefetching semantics defines a strategy to prefetch relationships. Depending
on it, Cayenne would generate different types of queries.
-The end result is the same - query root objects with related objects fully
resolved. However semantics can affect performance,
-in some cases significantly. There are 3 types of prefetch semantics, all
defined as constants in `org.apache.cayenne.query.PrefetchTreeNode`:
+Prefetch semantics defines a strategy to prefetch relationships. Depending on
semantics, Cayenne would generate
+different types of queries. The end result is the same - query root objects
with related objects fully resolved.
+However semantics can affect performance, in some cases significantly. There
are 3 types of prefetch semantics
+defined as constants in `org.apache.cayenne.query.PrefetchTreeNode`:
[source]
----
@@ -69,13 +68,7 @@ PrefetchTreeNode.DISJOINT_PREFETCH_SEMANTICS
PrefetchTreeNode.DISJOINT_BY_ID_PREFETCH_SEMANTICS
----
-There's no limitation on mixing different types of semantics in the same
query. Each prefetch can have its own semantics.
-`SelectQuery` uses `DISJOINT_PREFETCH_SEMANTICS` by default. `ObjectSelect`
requires explicit semantics as we've seen above.
- `SQLTemplate` and `ProcedureQuery` are both using `JOINT_PREFETCH_SEMANTICS`
and it can not be changed due to the nature of those two queries.
-
-===== Disjoint Prefetching Semantics
-
-This semantics results in Cayenne generatiing one SQL statement for the main
objects, and a separate statement for
+*Disjoint prefetch semantics* results in Cayenne generating one SQL statement
for the main objects, and a separate statement for
each prefetch path (hence "disjoint" - related objects are not fetched with
the main query).
Each additional SQL statement uses a qualifier of the main query plus a set of
joins traversing the
prefetch path between the main and related entity.
@@ -83,12 +76,10 @@ prefetch path between the main and related entity.
This strategy has an advantage of efficient JVM memory use, and faster overall
result processing by Cayenne,
but it requires (1+N) SQL statements to be executed, where N is the number of
prefetched relationships.
-===== Disjoint-by-ID Prefetching Semantics
-
-This is a variation of disjoint prefetch where related objects are matched
against a set of IDs derived from the fetched
-main objects (or intermediate objects in a multi-step prefetch). Cayenne
limits the size of the generated WHERE clause,
-as most DBs can't parse arbitrary large SQL. So prefetch queries are broken
into smaller queries.
-The size of is controlled by the DI property
`Constants.SERVER_MAX_ID_QUALIFIER_SIZE_PROPERTY`
+*Disjoint-by-ID prefetch semantics* is a variation of disjoint prefetch where
related objects are matched against a set
+of IDs derived from the fetched main objects (or intermediate objects in a
multi-step prefetch). Cayenne limits the
+size of the generated WHERE clause, as most DBs can't parse arbitrary large
SQL. So prefetch queries are broken into
+smaller queries. The size of is controlled by the DI property
`Constants.SERVER_MAX_ID_QUALIFIER_SIZE_PROPERTY`
(the default number of conditions in the generated WHERE clause is 10000).
Cayenne will generate (1 + N * M) SQL statements for each query using
disjoint-by-ID prefetches,
where N is the number of relationships to prefetch, and M is the number of
queries for a given prefetch
@@ -100,9 +91,7 @@ Both joint and regular disjoint prefetches may produce
invalid results or genera
The disadvantage is that query SQL can get unwieldy for large result sets, as
each object will have to have its own condition in the WHERE clause of the
generated SQL.
-===== Joint Prefetching Semantics
-
-Joint semantics results in a single SQL statement for root objects and any
number of jointly prefetched paths.
+*Joint prefetch semantics* results in a single SQL statement for root objects
and any number of jointly prefetched paths.
Cayenne processes in memory a cartesian product of the entities involved,
converting it to an object tree.
It uses OUTER joins to connect prefetched entities.
@@ -110,18 +99,43 @@ Joint is the most efficient prefetch type of the three as
far as generated SQL g
Its downsides are the potentially increased amount of data that needs to get
across the network between the application server and the database,
and more data processing that needs to be done on the Cayenne side.
-===== Similar Behaviours Using EJBQL
+`<<select,ObjectSelect>>` query supports all three types of semantics. You can
mix and match them in the same query for
+different prefetches.
+
+`<<sqlselect,SQLSelect>>` query supports "JOINT" and "DISJOINT_BY_ID". It does
not work with "DISJOINT", as the query does not provide
+enough information to Cayenne to build dependent prefetch queries. So
"DISJOINT" will be quietly ignored. "JOINT"
+prefetching requires a bit of effort shaping the SQL to include the right
columns in the result and label them properly
+to be convertable into object properties. The main rules to follow are:
-It is possible to achieve similar behaviours with <<EJBQLQuery>> queries by
employing the "FETCH" keyword.
+* Include _all_ columns from the root entity and every prefetched entity.
+* Label each prefetched entity columns as "dbRelationship.column".
+
+E.g.:
+
+[source, Java]
+----
+List<Artist> objects = SQLSelect.query(Artist.class, "SELECT "
+ + "#result('ESTIMATED_PRICE' 'BigDecimal' ''
'paintingArray.ESTIMATED_PRICE'), "
+ + "#result('PAINTING_TITLE' 'String' '' 'paintingArray.PAINTING_TITLE'), "
+ + "#result('GALLERY_ID' 'int' '' 'paintingArray.GALLERY_ID'), "
+ + "#result('PAINTING_ID' 'int' '' 'paintingArray.PAINTING_ID'), "
+ + "#result('t1.ARTIST_ID' 'int' '' 'paintingArray.ARTIST_ID'), "
+ + "#result('ARTIST_NAME' 'String'), "
+ + "#result('DATE_OF_BIRTH' 'java.util.Date'), "
+ + "#result('t0.ARTIST_ID' 'int' '' 'ARTIST_ID') "
+ + "FROM ARTIST t0, PAINTING t1 "
+ + "WHERE t0.ARTIST_ID = t1.ARTIST_ID")
+ .addPrefetch(Artist.PAINTING_ARRAY.joint())
+ .select(context);
+----
+
+`<<ejbql,EJBQLQuery>>` uses the "FETCH" keyword to enable prefetching:
[source, SQL]
----
SELECT a FROM Artist a LEFT JOIN FETCH a.paintings
----
-In this case, the Paintings that exist for the Artist will be obtained at the
same time as the Artists are fetched.
-Refer to third-party query language documentation for further detail on this
mechanism.
-
==== Data Rows
Converting result set data to Persistent objects and registering these objects
in the ObjectContext can be an expensive
