There can be request to fetch an Valid Amount matching set of parameter values for a particular OuterID. The request can contain multiple OuterIDs.
Currently our N1ql query ( using Java SDK ) consists of multiple UNNEST along with Key of the Outer Document itself.
something like this…
select from bucket use keys ( ) UNNEST parameters as param UNNEST params.baseAmount as amount where amount is valid for current Time;
Since there can be multiple requests on Outer IDs , we used union all clause to combine all of these N1ql to minimize I/O operations. The performance on the query ( say 10 unions ) has not been that good ( ~ 40 ms ), even though the document is fetched using key scan.
Can you please suggest is there any more room for improvement on the above query on top of KeyScan . Also what is the suggested approach when retrieving results for such document structure , should we use N1ql or sdk functions to fetch the appropriate data. I really do not want to fetch the document using key in memory and perform such complicated filtering , that would defeat the purpose of N1ql.
For security reasons i cannot post the exact query , i am sorry about that. Parameters are not array , but record is which contains list of param:values and its associated prices.
The reason to use Union ALL is because we have a complicated hierarchy of prices in our output structure and we have created an unique alias to each individual result and storing the same result in Map and pushing in back in our output hierarchy…
Using the SDK would be our next step , but as i mentioned above , this really beats the purpose of using N1QL if there is such a difference. I would like to better understand the limitations of N1QL before i go with the SDK options using keys
Understand that. In that case we can suggest generically because don’t know lot of information.
If parameters is not array why are you doing UNNEST parameters as param? You can avoid it. If you are not projecting UNNEST values you can check ANY syntax.
There is no limitations on N1QL, In your case you are not doing IndexScan and using KeyScan (data needs two hops) by directly fetching you have one hop but all processing of data needs in SDK.
Also if you executing this query repeatedly set adhoc=flase, If using 4.5.1 or above set pretty=false