I will be integrating the changes from the query-optimization branch into the trunk. If there are issues with these changes then you can report them as a comment to this issue or as a new issue. A description of the changes from the query-optimization branch follows.

Automatic prefetching

A queryoptimization option is added to application.ini, taking the following values:

• 0 - Disabled
  This uses lazy fetching of properties, which was used before. Properties will be fetched when they are used, using a separate query for every used property of every entity instance.
• 1 - Guided batch fetching
  A static analysis detects properties that are used on the iterator of for-loops during compilation. Prefetching code will be generated before for-loops. The generated code uses one query to prefetch a property for all collection elements that have the property uninitialized. This leads to one query per property used inside a for-loop. This is the default optimization mode.
• 2 - Guided batch fetching with joins
  Same as guided batch fetching, but uses joins the prefetch properties of the entities being fetched. This could remove the need to execute a query for the property that was joined at the risk of fetching more duplicate entities. Collection properties are never joined, because this is very likely to increase the number of duplicate entity fetches.
• 3 - Guided batch fetching with fixed batch size
  Same as guided batch fetching, but using a maximum batch size that limits the number of entities/collection that can be fetched by one query. This allows queries to be generated during launch, but may execute more queries, because batches are split up if they are too large for the generated queries. The maximum batch size is configured to be 10.
• 4 - Hibernate batch fetching
  This is a feature of Hibernate that also uses a maximum batch size, which is configured to be 10. When executing a lazy fetch query, Hibernates picks similar uninitialized entities/collection from a list, to be fetched using the same query. The list that Hibernate picks from is in the sequence that proxy classes for lazy properties are created in. The uninitialized proxies that are picked are preferably just after or before the property being lazy fetched.
• 5 - Hibernate subselect fetching
  Another feature of Hibernate that remembers the query that was used to fetch entities. When an uninitialized collection property is accessed on a entity from that query, then the query can be used as a subquery to fetch the collection property for all entities of the initial query. Only queries that fetch more than one entity are remembered, because otherwise it would just be lazy fetching with subqueries instead of primary key lookups.
• 6 - Hibernate subselect and batch fetching
  This combines the subselect and batch fetching features from Hibernate. Subselect fetching will be used whenever a subquery is available. Otherwise batch fetching will be used.
• 7 - Prefetching at arguments
  At the beginning of pages, templates and functions, prefetching code will be generated for the arguments. This also uses the static analysis to detect properties accessed on the arguments during compilation.

Changing the queryoptimization option in application.ini requires a rebuild of the application. Optimizations can also be disabled for a single request by adding ?disableopt to the end of the url, but this only works for the optimizations that generate code, so this does not work for the built-in Hibernate features.

Additions to ?logsql

• Also works for actions and ajax requests. The ?logsql query string gets passed on to actions and ajax calls automatically when the original request had a ?logsql query string.
• Shows the number of duplicate entity fetches, which can also be caused by collections containing the same entity more than once.
• Shows the number of hydrated objects per query, which is the number of entities that were returned that were not in the Hibernate session cache yet.
• Shows for each query the template that executed it, queries executed from functions will show the signature of the template that called them.

Collection properties

Previously calling add or remove on a collection property got desugared to addTo<property-name> and removeFrom<property-name> calls on the entity. This desugaring was not possible if the property was passed on to a function that accepted a collection as an argument, because the the entity that has the collection property does not need to be passed on to the function. This is fixed by giving every collection property its own collection implementation. The collection implementation will remember the entity on which the collection is defined and perform the code normally executed by addTo<property-name> and removeFrom<property-name>, which includes updates of the inverse relation and calling the function extensions of addTo<property-name> and removeFrom<property-name>.

Other changes

• CurrentTemplateDecl is no longer updated for local redefinitions before they are lifted, because before they are lifted they have the same genname and the local redefinition can override the global definition.
• Normally automatic flushing is enabled, which flushes any change back to the database before executing queries. This will execute dirty checking before executing queries, even when nothing was changed. To reduce the time spent on dirty checking, flushing is set to only occur just before the commit of the transaction. Flushing is set back to automatic whenever an entity is changed.
• The inverse side of bidirectional one-to-one properties are now implemented as NO_PROXY properties. This allows them to be fetched without having to fetch other inverse side bidirectional one-to-one properties that are defined on the entity.
• Column names are now saved case-sensitive (instead of capitalized) for the H2 database, so that case-sensitive queries (where column names have quotes) will work.
• Registered the static search<entity-name> functions as reserved, so that the static analysis for prefetching does not wait for them to be analyzed. This also changes the error message when you declare a global search<entity-name> function.
  Previous error: Global function with signature search<entity-name>(<arguments>) is defined multiple times
  New error: Function with name ‘search<entity-name>’ and <number-of-arguments> argument(s) collides with a generated function that you are not allowed to overwrite

Submitted by Christoffer Gersen on 21 September 2012 at 15:15