[
http://jira.nuxeo.org/browse/NXP-4040?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=59726#action_59726
]
Jim Brei commented on NXP-4040:
-------------------------------
original query
SELECT * from Document WHERE grzuid:uid = 'MediaRecord_RD3009926213_00001' AND
ecm:path STARTSWITH '/default-domain/sections' AND ecm:isProxy = 1
The starts with was added to restict to published. isProxy helped with
performance over nothing. We are not sure what isProxy does nor the full
impact of it.
> 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
