N1QL SELECT / UPDATE statement

SQL++
1

Hi,

I am using the following query, UPDATE bucket USE KEYS “myKey” SET field = “updatedValue” and I am really surprised by the execution time of it, which is around 870ms on a average of 10 execution!

When I run with EXPLAIN, it is using the keyScan, which, I think is correct as the key is provided in the n1QL query directly.

I am really surprised by this performance, as it should just do a direct access to the document… It seems to me that it will be faster for me to do a bucket.get(“myKey”), change the document and then bucket.upsert().

Any idea why this is so slow? There are 50000 documents in my bucket and a primary index exist. The test is run against a 1 node cluster on a local vagrant, with version 4.0 GA.

I also found out that a simple select, like SELECT field FROM bucket USE KEYS “myKey” is taking on average 35ms when I guess doing a bucket.get(“myKey”) and then only returning the filed in there will be a lot quicker too

Many thanks.

2

How are you measuring the execution times? Can you run both of these in the cbq shell and then post the reported times? Thanks.

3

I reworked most of my queries to not use primary indexes but mainly secondary indexes and this seems to have made this issue disappeared! Mainly the load on the server is less since not using primary indexes. So I was not able to reproduce the issue above.

What I can see now, is that to select a specific field in my document, I use a query like SELECT myField FROM myBucket USE KEYS “myKey”

This return in average around 3ms… This is calculated in my java code using a stopwatch.

Doing the same query with cbq takes on average 1ms, so it looks like the java driver and my java code add around 2ms…

For update, I am now doing a get / update / replace loop for other reason so do not use the N1QL query anymore.

Regards,
Laurent.