Hello,
I made some performance comparisons between SQL-2 and XPATH on
Jackrabbit 2.0 .
For instance when I have a jnt:news node and a jnt:translation subnode
per translated language and I want to return all child containers of a
node sorted by title.
A query doing this, looks like that:
SQL-2:
SELECT * FROM [jnt:translation] AS translation inner join [jnt:news] as
news on ischildnode(translation, news) WHERE ISCHILDNODE(news,
[/sites/ACME/home/page8/news]) and translation.[jcr:language] =
'${currentResource.locale}' ORDER BY
translation.[jcr:title_${currentResource.locale}]
XPATH:
/jcr:root/sites/ACME/home/page8/news/*/element(j:translation,
jnt:translation)[...@jcr:language = '${currentResource.locale}'] order by
@jcr:title_${currentResource.locale}
Now this are the average performance figures for *querying 10 sorted
nodes and iterating through them*:
*Number of news containers in the "news" list
* *Average time for SQL-2
* *Average time for XPATH
*
4
62ms
45ms
128
142ms
118ms
256
178ms
130ms
512
256ms
172ms
1024
326ms
249ms
A growing number of subnodes slows down the performance.
Now in our use case we have the actual news on a page and the archived
news in subpages. When searching through all of them I want to search
through descendant nodes and not just child nodes. So I used the
following query:
SQL-2:
SELECT * FROM [jnt:translation] AS translation inner join [jnt:news] as
news on ischildnode(translation, news) WHERE ISDESCENDANTNODE(news,
[/sites/ACME/home/page8]) and translation.[jcr:language] =
'${currentResource.locale}' ORDER BY
translation.[jcr:title_${currentResource.locale}]
XPATH:
/jcr:root/sites/ACME/home/page8/news//element(*,
jnt:news)[j:translation/@jcr:language = '${currentResource.locale}']
order by j:translation/@jcr:title_${currentResource.locale}
The main news page had 1024 news and there were two archive pages having
288 news each and my query just had to return 10 news sorted by title.
The average time to query and iterate through the result
is: SQL-2: 815ms XPATH: 200ms
This shows that SQL-2 ISDESCENDANTNODE query performance is very bad.
Are there already plans or ongoing work to tune SQL-2 queries especially
for ISDESCENDANTNODE ?
Regards,
Benjamin
