QueryAsync doesn't work

.NET SDK
1

Hello,

I’m trying the following code:

                    var result = await appGlobals.couchbaseServerInstance.QueryAsync<dynamic>(
                        "DELETE from `" + bucketName + "` where META().id LIKE '%UserID_$1'",
                        options => options.Parameter(deleteForUserID)
                    );

result.MetaData.Status shows Success, but nothing is being deleted.
When I run this query manually in the UI, it works.
I’m on SDK 3.3.3 with .NET Core 6 and CB 6.6.0
Latest 3.4 SDK did not work either.

When reverted to .NET Framework 4.8 + SDK 2.5.8 I am able to send this query successfully and the documents are deleted

2

@alon.schachter

I believe your issue lies in how you’re using the parameter. In my testing, the parameter doesn’t work as a string interpolation value, but only as a whole literal in the query. So you’re actually trying to delete %UserID_$1 not %UserID_123. Instead try WHERE META().id LIKE $1 and build the string in the positional parameter.

On a secondary note, you’re going to probably run into performance issues with that query at scale. A LIKE clause with a leading % is essentially unindexable, so this query is going to scan every document in the collection every time it is executed.

1 Like
3

Yeah that was the issue.

How would you suggest deleting all documents belonging to a user then? (assuming names of documents are dynamically generated)

And site note: why does the query return an error when an index isn’t created if it isn’t going to use an index anyway

4

To be clear, that query will use a primary index, which is an index of the keys of all documents in the bucket. The issue is the efficiency with which it can use it. Because that index is sorted alphabetically by key, a LIKE with a % in the front must scan through the entirety of that index and do a string comparison on each key. It cannot use the B-tree (or whatever internal structure it uses, I’m honestly not sure) to get a quick range of results. If that bucket gets very large this can get slow. This will be further exacerbated if the index is not 100% memory resident, as each execution must page from disk.

My recommendation would be to create an index on a field that more closely matches what you’re trying to delete. I’m going to make an assumption that your goal is to delete all documents related to a particular user, regardless of the document type. To do this, I’d instead recommend including a userId attribute on all such documents and create an index on this attribute.

1 Like