Performance of Search Predicates vs SQL2 queries | Community
Skip to main content
LinearGradient
LinearGradient
Level 6
October 16, 2015
Solved

Performance of Search Predicates vs SQL2 queries

  • October 16, 2015
  • 4 replies
  • 4930 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Ove_Lindström

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

4 replies

O
Ove_LindströmAccepted solution
Level 6
October 16, 2015

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

J
Adobe Employee
JustinEd3Adobe Employee
Adobe Employee
October 16, 2015

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
LinearGradientAuthor
Level 6
October 16, 2015

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.

O
Ove_Lindström
Level 6
October 16, 2015

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

Terms & ConditionsAccessibility statement

Loading footer...