Prepared statement - AddPositionalParameter() for array

.NET SDK
1

Hello.

Let’s say I have a query like this:

select m.body.*
from `mybucket` m
use keys ['doc::1001', 'doc::2001', 'doc::3001', 'doc::4001', 'doc::5001', 'doc::6001', 'doc::7001', 'doc::8001', 'doc::9001']

Question 1:
With C# SDK, when constructing prepared statement for the above query using QueryRequest, I could not figure out how to pass array of keys as positional parameters.

This does not work:

string q = "select m.body.* from `mybucket` m use keys $1";
string[] keys = {"doc::1001", "doc::2001", "doc::3001", "doc::4001", "doc::5001", "doc::6001", "doc::7001", "doc::8001", "doc::9001"};
IQueryRequest query = new QueryRequest().Statement(q).AdHoc(false);
query.AddPositionalParameter(keys);

My work-around is like this:

string q = "select m.body.* from `mybucket` m use keys [";
string[] keys = {"doc::1001", "doc::2001", "doc::3001", "doc::4001", "doc::5001", "doc::6001", "doc::7001", "doc::8001", "doc::9001"};
for (int i = 1; i < keys.Length; i++) {
     q += $"${i}";
}
q += "]"; // close the square bracket

IQueryRequest query = new QueryRequest().Statement(q).AdHoc(false);
foreach (var k in keys) {
    query.AddPositionalParameter(k);
}
// query execution code here...

What am I missing?

Question #2:
The number of keys can vary from one to a few hundreds. Given this fact, is it even beneficial to use prepared statement for this query?

Thanks in advance for your answer.

2

  1. I’m not sure if USE KEYS can be used with prepared statements or not, never tried it.

  2. The reason I haven’t tried it is that this is a very non-performant way of getting multiple documents if you already know the keys, and something of an anti-pattern. Instead, you should use the multi-get APIs on the bucket, which accept an array of document keys. This avoids the need to pass through the query engine and HTTP altogether, instead using the optimized binary protocols directly to the data nodes.

2 Likes
3

Thank you for the quick reply.

So yes, in production I use regular query API where I pass a pre-built n1ql statement. I am in the phase of optimizing performance of my application for a new version. This is more of an experiment to explore ways to do that.

I guess I don’t totally understand why and when to use prepared statements.

The “problem” with multi-get is that it returns list of complete and whole documents where as you noticed I only need the “body” item of a document. Let’s say “body” is a very small fraction of the a whole document. In my actual application I need multiple sub-documents. I don’t feel like it is efficient to load everything when what I need is just some items.

Thanks.

4

https://docs.couchbase.com/dotnet-sdk/2.7/subdocument-operations.html

1 Like
5

Thanks for your input @ingenthr.
Unless, I misunderstand it, LookIn() can only query a single document - yes?

In any case, my main objective is to find the best way to do query as in pattern I provided. I thought it is a better practice if I have a prepared statement with this particular query.

So back to my question #1 - how do you pass an array to a prepared statement using AddPositionalParameter()?

Say now I have a query like this:

select m.item1.*, m.item2.subitem5.*
from `mybucket` m
where m.color in ['red', 'blue', 'indigo', 'light-orange']

The list of colors is the parameter that I want to pass using AddPositionalParameter().

Thanks.