GitHub user mike-jumper opened a pull request: https://github.com/apache/incubator-guacamole-client/pull/60
GUACAMOLE-76: Fix N+1 problem when querying connection group tree. Speed of the connection group tree REST resource fell dramatically with the addition of sharing profiles. This is because the `getSharingProfileIdentifiers()` function of `Connection` issues a new query for every call, and is not affected by retrieval of connections in batches via `getAll()` on the `Directory<Connection>`. This change addresses this by retrieving child object identifiers when the parents are retrieved. If multiple parents are retrieved, the identifiers of each of their children is retrieved with *one query* (rather than *N queries*). This is accomplished through issuing multiple `SELECT` statements within the same logical query defined in the MyBatis mapping. Beware that while PostgreSQL will handle multiple statements within a single query by default, the MySQL JDBC driver requires a special `allowMultiQueries` property for this. We should look into making the connection tree retrieval process more efficient, as there are definitely ways it could be sped up (use breadth-first only if attempts to retrieve the entire directory contents at once fail?), but this should fix things with respect to release-blocking regressions. Query performance should be back to where it was. You can merge this pull request into a Git repository by running: $ git pull https://github.com/mike-jumper/incubator-guacamole-client connection-group-tree-speed Alternatively you can review and apply these changes as the patch at: https://github.com/apache/incubator-guacamole-client/pull/60.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #60 ---- commit a83b5c585c5df63e6254dc62a88d1752fbcfa88e Author: Michael Jumper <mjum...@apache.org> Date: 2016-08-20T05:03:15Z GUACAMOLE-76: Query child object identifiers directly. commit 2e3d3c4f5fba477ede71ea8a831a2c66dd0b4f37 Author: Michael Jumper <mjum...@apache.org> Date: 2016-08-20T19:41:16Z GUACAMOLE-76: Set "allowMultiQueries" for MySQL. commit 5794338b8d46ea36fc99716f13bba26944c5934d Author: Michael Jumper <mjum...@apache.org> Date: 2016-08-20T19:41:48Z GUACAMOLE-76: Clean MySQL and PostgreSQL modules for consistency. ---- --- If your project is set up for it, you can reply to this email and have your reply appear on GitHub as well. If your project does not have this feature enabled and wishes so, or if the feature is enabled but not working, please contact infrastructure at infrastruct...@apache.org or file a JIRA ticket with INFRA. ---