Hi, 2009/12/6 Weston Bustraan <[email protected]>: > Thanks! I tried using ISDESCENDANTNODE, but for some reason it wasn't > working. Using your syntax worked fine. > > However, there seems to be a significant performance difference > between the same query in JCR-SQL2 and XPATH > > So, for example, this comes back immediately: > > /jcr:root/fm:profiles/com/gmail/wbustraan/*...@fm:session = > '3b7856fb-6a5d-427d-a7d6-00af15d7178a'] > > While this takes about 10 seconds: > > select * from [nt:base] where > isdescendantnode([/fm:profiles/com/gmail/wbustraan]) and [fm:session]= > '3b7856fb-6a5d-427d-a7d6-00af15d7178a' > > Is this a known issue with the new JCR-SQL2 queries or am I doing > something wrong?
this is somewhat a known issue. the current implementation of QOM/SQL2 is still quite basic and has only a few optimizations. regards marcel > On Fri, Dec 4, 2009 at 4:12 AM, Philipp Bunge <[email protected]> wrote: >> Hi Wes >> >>> I have a bit of a dilemma. I'm working with Jackrabbit 2.0b3 and >>> trying to migrate to JCR2.0. However, since the XPATH query syntax is >>> deprecated, I'm investigating how to go about writing queries in >>> JCR-SQL2. Unfortunately, I haven't come up with many examples or >>> tutorials for the new JCR-SQL2 syntax. >> >> There really aren't many tutorial around as far as I know. I use the >> railroad diagrams that Thomas Müller created quite a lot as a >> reference however: http://www.h2database.com/jcr/grammar.html >> >>> One of the scenarios that I'd like to use frequently is searching a >>> subtree, i.e. starting at a particular node and searching all >>> descendants for certain property values. This is trivial to accomplish >>> in XPATH, but I can't figure out the correct (and efficient) way to do >>> it using SQL2. Can any one help me out with an example? >> >> You probably want something as follows: >> >> SELECT * FROM [nt:unstructured] >> WHERE ISDESCENDANTNODE([/the/particular/node]) >> AND yourproperty = "foo" >> >> >> Cheers, >> Philipp >> >
