Slow NXQL queries

Bertrand_Pestre — Fri, 26 Jul 2019 15:13:22 GMT

I have some NXQL queries that are pretty slow. Using this https://doc.nuxeo.com/nxdoc/monitoring-slow-nxql-queries/ I was able to get some of the slowest NXQL queries This one took 870 ms:

QueryFilter(principal=myOrganisation, limit=0, offset=0) count total results UNLIMITED 
query SELECT * FROM Document 
WHERE (myOrganisation.common:unpublished = 0) 
	AND (ecm:isVersion = 0) 
	AND (myOrganisation.common:removed = 0) 
	AND (dc:source = 'MyOrganisation') 
	AND (myOrganisation.common:migrating = 0) 
	AND (ecm:path STARTSWITH '/default-domain/workspaces/MyOrganisation/organisations/1011/legal' 
		OR ecm:path STARTSWITH '/default-domain/workspaces/MyOrganisation/organisations/1011/business' 
		OR ecm:path STARTSWITH '/default-domain/workspaces/MyOrganisation/organisations/1011/projects/5180')

I notice that the more clauses ecm:path STARTSWITH '/default-domain/workspaces... are present, the slowest is the query. An other similar request with approximately 40 ecm:path STARTSWITH ... clauses took 3200 ms.

Is it a bad practice to write NXQL queries like this ?

Re: Slow NXQL queries

Florent_Guillau — Tue, 30 Jul 2019 13:52:02 GMT

Yes it's bad practice because STARTSWITH queries are inherently costly (as you can imagine). You should review your data modeling to make it more amenable to efficient queries.

topic Re: Slow NXQL queries in Nuxeo Forum

Slow NXQL queries

Re: Slow NXQL queries