LET variables very slow performance

SQL++
1

I have a query whose SELECT clauses are fairly involved:

SELECT 
ARRAY (COALESCE(TO_STRING(a.cids[ARRAY_LENGTH(a.cids) - 1]) || '~', '') || TO_STRING(a.v)) FOR a IN bucket.answers WHEN a.q = 'Question1234' END AS `Question1234`, 
ARRAY(COALESCE...END AS 'Question1235',
ARRAY(COALESCE... END AS 'Question1236'

The user may need to sort on any single select column, so my ORDER BY clause may look something like:

ORDER BY ARRAY (COALESCE(TO_STRING(a.cids[ARRAY_LENGTH(a.cids) - 1]) || '~', '') || TO_STRING(a.v)) FOR a IN bucket.answers WHEN a.q = 'Question1234' END

I came up with the idea of defining a LET variable to reference each complex SELECT clause, so I could then ORDER BY the LET variable name instead of re-listing the entire SELECT clause. This leaves my N1QL statements generally having between 4 and 8 chained LET vars defined.

Trouble is, these LET vars are killing the performance of my queries. Viewing the execution plan for such queries reveals that over 90% of the processing time of the query is in defining the LET vars.

By NOT defining LET vars and instead duplicating the SELECT clause over to the ORDER BY clause, my query performance instantly improves by well over 500%.

This leads me to believe that LET var performance is an issue with couchbase. Yet I see no reference to performance issues with LET in the documentation.

Can you confirm that generating multiple chained LET clauses is, in fact, a performance killer? And if so, would you please consider making a note of this in the documentation for LET?

2

It depends. As don’t have full query plan hard to tell.

LET variables can be derived from document, can be chained. If Any let variables used in WHERE it will inline and choose index selection. Also variables evaluated before WHERE FILTER. If not used in WHERE it evaluated post FILTER to save evaluation. If constant and So happens FILTER is evaluate false, No documents will be fetched.

Above example uses ARRAY (i.e looping construct), Only evaluate when need saves lot of time.

LET variables in ORDER BY will not use Index Order.

If you are using only in ORDER BY and Projection, define that in projection and use ORDER BY projection alias

 SELECT lower(t.name) AS lower_name 
FROM default AS t 
WHERE .....
ORDER BY lower_name