viernes, 11 de marzo de 2016

JCR-SQL2 optimize queries samples

Queries run on Magnolia 5.x


1. Search for specific Item type 

i.e. [mgnl:page] instead of using the default one [nt:base]
Example: Search for pages with stkNews template

select * from [nt:base] as t where isdescendantnode(t, '/demo-project') and  t.[mgnl:template]='standard-templating-kit:pages/stkNews'

This query takes 18ms to return.

select * from [mgnl:page] as t where isdescendantnode(t, '/demo-project') and  t.[mgnl:template]='standard-templating-kit:pages/stkNews'

This query takes 3ms to run.


2. Avoid the use of isdescendantnode if not necessary.

Example: Searching in data module, two different types of data with same property

SELECT * from [nt:base] AS t WHERE  ((ISDESCENDANTNODE(t, '/folder1type1')   AND [jcr:primaryType] = 'type1') or (ISDESCENDANTNODE(t, '/folder2type2')   AND [jcr:primaryType] = 'type2')) and t.categories = 'c6f40746-5be4-44c5-9eb1-0f5e59133350'

This query takes more than 6000ms to run

SELECT * from [nt:base] AS t WHERE   [jcr:primaryType] = 'type1' or [jcr:primaryType] = 'type2' and t.categories = 'c6f40746-5be4-44c5-9eb1-0f5e59133350'

This query takes 13ms to run, in this case we are looking for type1 that is just under folder1type1 and type2 that is just in folder2type2, so the isdescendantnode is redundant.


3. Performance

In order to check the queries performance and see if you need to optimize them, see previous post on http://tmiyar.blogspot.com.es/2012/06/queries-logging.html


4. query samples

* get latest modified pages in demo-project, here you need to know that any time you modify any component in the page, that page gets its modification date updated. That is why we only need to ask for mgnl:page
select * from [mgnl:page] as t where isdescendantnode(t, '/demo-project') order by t.[mgnl:lastModified]
the way to limit the result set is by code query.setLimit(3); before calling query.execute();

* find where a control is used in the configuration
select * from [mgnl:contentNode] as t where t.class like '%SelectFieldDefinition'
In this case you can decide to get the result type mgnl:content, you can do it in the jcr query tool.

* for queries that rely on a path with wildcards there is no other way that to use isdescendantnode and inschildnode joins that makes queries a bit more complex, in that cases I would recommend to use xpath as is not deprecated, it is faster and I dont think they will remove it.


Note: Nodes get cached by Jackrabbit, second time you run the query in our tool will be faster, keep that in mind when doing your tests.