This is an automated email from the ASF dual-hosted git repository.
aadamchik pushed a commit to branch STABLE-4.1
in repository https://gitbox.apache.org/repos/asf/cayenne.git
The following commit(s) were added to refs/heads/STABLE-4.1 by this push:
new 72f69a9 prefetch semantics docs chapter reorg, clarification
72f69a9 is described below
commit 72f69a9a0c3115957bec69572703a6b78f707db7
Author: Andrus Adamchik <[email protected]>
AuthorDate: Thu Jul 30 10:03:33 2020 +0300
prefetch semantics docs chapter reorg, clarification
---
.../asciidoc/_cayenne-guide/part2/queries/sql.adoc | 1 +
.../docs/asciidoc/_cayenne-guide/part2/tuning.adoc | 66 +++++++++++++---------
2 files changed, 41 insertions(+), 26 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 7184aa0..e5dbd6e 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,7 @@
// 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 b8f24bd..c89ed09 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
@@ -56,11 +56,10 @@
query.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
-
-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`:
+A strategy to prefetch relationships is defined by prefetch "semantics".
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:
+
+* Include _all_ columns from the root entity and every prefetched entity.
+* Label each prefetched entity columns as "dbRelationship.column".
-It is possible to achieve similar behaviours with <<EJBQLQuery>> queries by
employing the "FETCH" keyword.
+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
