I was quite fascinated by the world of XPath and the way i could configure a quick map and pass it to the queryBuilder interface to run a simple query and parse results. All my dreams shattered when i was told Adobe has deprecated the support of XPath queries and you are now required to use the JCR – SQL2 queries. The next search class that i ended up writing was for JCR and all was good until we really started pushing the content. The response time of the queries climbed up rapidly and we started feeling the pain of working with JCR queries.
JCR queries shouldn’t be used at all for multiple paths, by this i mean if you want to search under two different paths with one query you will end up with a very high response times. XPath queries or Query Builder seems to do that fine. For simple queries, you won’t find much difference between the response times of JcrSql queries and Xpath queries but as you build complex queries and adding multiple predicate you will soon start to find yourself in deeper and deeper holes.
For this very reason, i believe Adobe has not been able to deprecate Xpath or Query builder as everything is backwards compatible.
The performance of XPath isn’t very good, if you put it under heavy load but it has to be heavy load more than 500 concurrent search requests or something. The average response times for us for not so complex queries under load were approx 5 seconds under very heavy load for us. We then decided to use listChildren method of Resource interface as this is quite fast. 100x faster and it is not an exaggeration. If you are building such a system, i hope you will get your caching strategies right. Try to save your query results for easier retrieval afterwards and make less number of queries as you can offload a lot of that to the caching server.
So, build your systems with some of advice mentioned above and you should be ok.