-
1. Re: slow SQL2 query
bwallis42 Apr 12, 2013 7:39 AM (in response to bwallis42)I think this problem has been covered in this post, https://community.jboss.org/thread/222854?tstart=0
Join performance is just not particularly good for the moment.
-
2. Re: slow SQL2 query
rhauch Apr 12, 2013 7:54 AM (in response to bwallis42)I seem to recall that some one mentined that there is a way to get a query plan from modeshape but cannot find out how now. I'm running my query via the rest API (using curl) and I do get the expected result (15 episode nodes).
I don't think the REST API has a way to get the query plan. Feel free to log an enhancement request.
-
3. Re: slow SQL2 query
hchiorean Apr 12, 2013 8:24 AM (in response to rhauch)Atm, one can retrive the query plan by using the ResultSet and ModeShape's API, like so: (org.modeshape.jcr.api.query.QueryResult)result).getPlan()
-
4. Re: slow SQL2 query
bwallis42 Apr 17, 2013 11:25 PM (in response to bwallis42)I've tried to come up with some alternative queries that don't involve joins
{code}
select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where isdescendantnode(e,(select p.[jcr:path] from [inf:patient] as p where p.[inf:masterId] = '00010014'))
{code}
This doesn't work, it gets the error:
is not well-formed: Expecting ')' but found 'select' at line 1, column 93: isdescendantnode(e,( ===>> select p.[jcr:path]
The documentation sort of suggests this should work, for subqueries it says
Additionally, because subqueries appear on the right-hand side of an operator, all subqueries must return a single column, and each row's single value will be treated as a literal value.
and isdescendantnode() says
The descendant-node constraint stipulates that the node appearing in the selector with the given name is a descendant of a node with a path that matches the literal path provided.
but I suppose a literal value is not quite a literal path.
Next I tried using jcr:path and like, the following query runs in less than 50ms and returns the results I expect (the statement "select p.[jcr:path] from [inf:patient] as p where p.[inf:masterId] = '00010014'" returns the path /inf:patient[265])
{code}
select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where e.[jcr:path] LIKE '/inf:patient[265]%'
{code}
Which got me wondering if I could use a subquery to build up the value for the RHS of the LIKE constraint (since it needs the trailing '%'). I tried the following
{code}
select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where e.[jcr:path] LIKE (select concat(p.[jcr:path],'%') from [inf:patient] as p where p.[inf:masterId] = '00010014')
or
select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where e.[jcr:path] LIKE (select p.[jcr:path]||'%' from [inf:patient] as p where p.[inf:masterId] = '00010014')
{code}
but they return errors since the concat or its alias '||' are not supported.
is not well-formed: Expecting \"FROM\" but found \"(\" at line 1, column 105: LIKE (select concat ===>> (p.[jcr:path],'%') f
Does anyone have any other possibilities that I haven't thought of?
thanks.
-
5. Re: slow SQL2 query
bwallis42 Apr 17, 2013 11:28 PM (in response to bwallis42)Just got the dreaded "moderator" message posting a reply and this time the reply has not turned up, wonder if this one will get through...
[edit: Yep, just my first one that failed, oh well, just have to wait I suppose]
-