Performance of Search Predicates vs SQL2 queries

[LinearGradient]

Hi,

Are there any performance differences between Search Predicates and SQL2 queries in CQ 5.6?

Which one is the recommended API for querying for specific nodes in the CRX?

Thanks.

[Ove_Lindström · Accepted solution]

Short answer: No.

Longer answer: No, since the Search Predicates generates SQL2 queries in the backend there are, in theory, no difference. However, my experience is that developers tend to be better in using Predicates in Java code and get better preformance out of those queries than when they use SQL2 directly. My quess is that this is because the fellows that has been writing the Predicates actually know what they are doing and optimize the queries and most of us does not understand SQL2 well enough to beat them.

I have also noted that in most cases when loops are used to create queries, the actuall creation of the query is faster using Predicates than SQL2 string concatenation. Som my advice is: Use Search Predicates if you can. Use SQL2 queries if they are static and easy to understand.

/Ove

[justin_at_adobe]

Hi,

If you are referring to QueryBuilder (http://dev.day.com/docs/en/cq/current/dam/customizing_and_extendingcq5dam/query_builder.html), then this always generates an XPath query. It's actually part of the API.

In terms of performance, in CRX2/AEM 5.6.1, XPath performance will be generally faster than JCR-SQL2 (they are effectively two separate query engines). In Oak/AEM 6.0, performance will be approximately equivelant -- XPath queries are translated into JCR-SQL2.

Queries using the QueryBuilder will always be slower than the corresponding XPath query. How slower depends upon the query. For large result sets, it can be significant. However, there are some queries which you can only run using QueryBuilder.

Justin

[LinearGradient]

Thanks for the comment Ove.

Just one question: is there anywhere officially indicated that Predicates are converted to SQL 2 queries? Because a colleague of mine thought the reverse is true: that SQL 2 queries are converted to predicates in the backend.

[Ove_Lindström]

LinearGradient wrote...

Thanks for the comment Ove.

Just one question: is there anywhere officially indicated that Predicates are converted to SQL 2 queries? Because a colleague of mine thought the reverse is true: that SQL 2 queries are converted to predicates in the backend.

Well... converting structured code to SQL2 is easy. Converting text to code... a bit harder.

Empirical studies. When I use Predicates, if fires a query, sometimes XPATH, sometimes SQL.

/O