Performance of NXQL query in Nuxeo 5.2rc1
-----------------------------------------
Key: NXP-4040
URL: http://jira.nuxeo.org/browse/NXP-4040
Project: Nuxeo Enterprise Platform
Issue Type: Problem
Components: Core SQL Storage
Affects Versions: 5.2 RC
Reporter: Jim Brei
Assignee: Thierry Delprat
Priority: Blocker
Regarding your immediate problem, if I'm not mistaken you have the
following query that takes 52 seconds:
SELECT DISTINCT "_nxhier"."id" 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 "grzimeks_uid" ON "hierarchy"."id" =
"grzimeks_uid"."id" WHERE "hierarchy"."primarytype" IN ($1, ..., $48)
AND "proxies"."id" IS NOT NULL AND ("grzimeks_uid"."uid" = $49) AND
(NX_IN_TREE("_nxhier"."id", $50)) AND
NX_ACCESS_ALLOWED("_nxhier"."id", $51, $52)
It would be useful to get the original NXQL query corresponding to
this, although we can mostly reconstitute it.
The speed of this query may be improved a lot if some circumstances
are met. The fist point below is the main culprit if the query plan
analysis is correct.
1. If you don't use proxies, then the proxy part of the query (LEFT
JOIN proxies) can be removed.
You get this effect by adding
ecm:isProxy = 0
to the original NXQL query.
2. Your query does a NX_IN_TREE which means the original query has a
ecm:path STARTSWITH something. If you can remove this check and
replace it with something else (that will depend on your data model)
then the query speed will improve. This will likely be improved for
the next version of Nuxeo.
3. If you don't need the security checks (NX_ACCESS_ALLOWED), then
query speed can be improved again. As above, this really depends on
your data model and security model.
--
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
