N1QL vs. Views performance with Ruby sdk

SQL++
1

To test out N1QL vs. Views I updated one of our data analytics ruby scripts to N1QL to run some benchmarks. This script makes about 15k lookups for individual documents (using .get in the views version and select * use keys in n1ql) and then about 7k selects that return between 0-500 documents each. Also, the cb instance is offline while running the scripts (that is, nothing is being added to the db, so the indexes aren’t being rebuilt). I’m using 4.5.0-2601 Community Edition (build-2601) running on a development laptop.

When I run it with views, it takes roughly 30 seconds. When I run it with n1ql, it takes roughly 45. The standard deviation is much higher with n1ql than with views as well.

It’s not clear at this point where exactly the slowdown comes, but before I go diving in, is this what I should expect or should I be seeing faster times with n1ql?

Thanks,

James

2

How are you obtaining the primary keys for both views and N1QL.

3

They come from an external source (another DB).

4

@james, can you try 4.5.1? N1QL fetch performance was improved in 4.5.1.

5

Will do…

Jhk

padding… That should be 20.

1 Like
6

@james,

  1. what’s your adhoc setting? true/false? @mnunberg, what’s the right way to set this in Ruby SDK?
  2. Since you’re getting up to 500 documents in a single statement, you can increase the pipeline-batch size to higher level (to 256 or 512).
    See: N1QL 200x slower than views for simple queries - #39 by keshav_m
1 Like
7

I’m not seeing 4.5.1 on the dl page, just dev preview 4.6 (we’re using community).

Using 4.6 dev preview brings the n1ql queries to within a second or two of views (with a much tighter standard deviation).

8

Yes, the download page is unclear. If you click on the button, you get current release 4.5.1.

@prasad, please arrange for the download page to separate preview releases from latest current release. Latest current release should be highlighted.

9

Thanks @keshav_m

From looking at the gem source, it’s not clear to me that you can set the adhoc field with the current ruby sdk.

10

@james which Ruby SDK are you using? Note that at the moment, there is no official Couchbase SDK for Ruby. @avsej used to maintain the 1.x, which went end-of-life in 2016. Adding support for prepared statements shouldn’t be too difficult to whichever one you’re using, but that’s not something we directly build/maintain. We can probably help if you need it though.

11

I’m doing these tests with 1.3.14. We’ve been using 1.3.13 in production for a couple of years now with no problems (using views). If we had more resources, we’d offer to help spruce it up, but we’re pretty strapped right now. I’ve heard rumors of a 2.0 sdk that we would be very happy to see…

12

hi @james, @geraldss,
The downloads page for Enterprise Edition does have both 4.5.1 and 4.6DP releases. CE has only 4.5 and 4.6DP, as the release version of CE is several months behind EE. CE also do not include the latest bug fixes.
https://developer.couchbase.com/documentation/server/4.5/introduction/editions.html

1 Like