jackrabbit-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Benjamin Papez <jahia.pa...@gmail.com>
Subject Performance of SQL-2 versus XPATH
Date Fri, 12 Mar 2010 11:05:10 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message