Push down Dynamic Subquery values to the indexer

Problem Statement

• I have a query with a sub query returning keys for an array.
• The sub query takes 90 ms to execute
• If I plug in the values the sub query returned to the main query, it takes 16 ms to execute.
• However, the query by itself takes > 2000 ms to execute.
• Why is the execution time that much greater than the sum of its parts?

Explanation

The query execution time is indeed much greater than the sum of its parts, and this is working as designed!

The short answer is, when you execute a query with the sub query supplying the values dynamically and not statically or parameterized, the values returned by the sub query are not pushed down to the indexer, but the results fetched from the Data Service and then the filter is applied, by the Query Service and results are returned.

Workaround

The only way to make it faster, is to make the results returned by the sub query static instead of dynamic. This means, the query would have to be broken up and the application itself issues 2 queries instead of 1.

Required

In order to avoid rewriting the app and breaking down the query, the Query service should evaluate the Subquery and push the values down to the indexer.