SQL-2 select

[Stanleyor]

Hi

There is a structure:

/content/site/ gb/ - en_GB ch/ - it_CH - fr_CH - de_CH fr/ - fr_FR be/ - nl_BE - de_BE - fr_BE

The task is: retrieve all language codes (for example nl_BE, de_BE...)

I wrote the SQL-2 select:

SELECT * FROM [cq:Page] as r RIGHT OUTER JOIN [cq:Page] AS c ON ISCHILDNODE(c, r) WHERE ISCHILDNODE(r, '/content/site')

and it does exactly what I need. The result is:

/content/site/gb/en_GB /content/site/ch/it_CH /content/site/ch/fr_CH /content/site/ch/de_CH ...

Although, the select is slow. It takes approximately 500 ms. I believe that's too much.

Does anyone know how to optimize this select or maybe there's another way to go? 

Thanks in advance

[smacdonald2008]

Here is good information on SQL 2 and other JCR information:

http://blog.liip.ch/archive/2012/06/26/jackrabbit-and-its-two-sql-languages-some-findings.html

[justin_at_adobe · Accepted solution]

You should use node iteration for this - not query. The query engine is optimized for property, not path, lookup.