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?