default TREE_CHILDREN querymodel generates a very expensive complex JOIN when
browsing folders with many documents (>5000)
--------------------------------------------------------------------------------------------------------------------------
Key: NXP-3206
URL: http://jira.nuxeo.org/browse/NXP-3206
Project: Nuxeo Enterprise Platform
Issue Type: Bug
Components: Web Foundations
Affects Versions: 5.2 M4
Reporter: Olivier Grisel
Assignee: Olivier Grisel
Priority: Major
Fix For: 5.2
When using the core sql search with the querymodel based tree manager, the
default TREE_CHILDREN querymodel with definition:
<queryModel name="TREE_CHILDREN">
<pattern>
SELECT * FROM Document WHERE ecm:parentId = ?
AND ecm:isProxy = 0
AND ecm:mixinType = 'Folderish'
AND ecm:mixinType != 'HiddenInNavigation'
AND ecm:isCheckedInVersion = 0
AND ecm:currentLifeCycleState != 'deleted'
</pattern>
<sortable value="true" defaultSortColumn="dc:title"
defaultSortAscending="true" />
<max>50</max>
</queryModel>
Generates the following SQL request in the backend:
SELECT DISTINCT "_nxhier"."id", "dublincore"."title" FROM "hierarchy" "_nxhier"
LEFT JOIN "proxies" ON "proxies"."id" = "_nxhier"."id" JOIN "hierarchy" ON
("hierarchy"."id" = "_nxhier"."id" OR "hierarchy"."id" = "proxies"."targetid")
LEFT JOIN "dublincore" ON "hierarchy"."id" = "dublincore"."id" LEFT JOIN
"versions" ON "_nxhier"."id" = "versions"."id" LEFT JOIN "misc" ON
"hierarchy"."id" = "misc"."id" WHERE "hierarchy"."primarytype" IN ('NewFolder',
'Workspace', 'Section', 'SectionExt', 'OrderedFolder', 'FolderTemplate',
'WorkspaceRoot', 'Domain', 'TemplateRoot', 'TextoFolder', 'GestionRoot',
'SectionOrg', 'Folder', 'SectionRoot') AND ("_nxhier"."parentid" =
'fa59bf57-d89c-4bb6-92bd-6838f54e3ace') AND ("versions"."id" IS NULL) AND
("misc"."lifecyclestate" <> 'deleted') AND NX_ACCESS_ALLOWED("_nxhier"."id",
'{administrators,Administrator,Everyone}',
'{Browse,Ecriture,Gestion,Lecture,Read,ReadProperties,ReadRemove,ReadWrite,Everything}')
ORDER BY"dublincore"."title";
This request takes more than 20s on a folder with 20000 documents. The culprit
is the LEFT JOIN on the proxies table which is useless to compute the tree
nodes since folders cannot be proxies in the default implementation of Nuxeo.
Adding the predicate "ecm:isProxy = 0" to the TREE_CHILDREN definition make the
same request last around 200ms.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://jira.nuxeo.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
