N1QL Performance

SQL++
1

Hello

I install two test servers with couchbase 3.0 and couchbase-query in a cluster.
Now i import 100.000 items.

I am start a query and stop the execution time - 8-10 seconds.

Can i optimize the query is not very fast…

Best regards
Tino

1 Like
2

First, performance is certainly not part of the preview of the technology. We are working on a new indexing strategy that will help with performance but that is in the works. so you will be able to tune the query in future.

3

Hi Tino,

Are you using an index?

Use EXPLAIN to make sure, or post the results of EXPLAIN here.

And as Cihan said, this is a preview release.

Thanks.

4

How i can create a index? i have found to create a view but no index.
I know it is a preview but i can not found a timeline for a release canidate.

At the moment i try a alternative with elasticsearch.

5

I have found the way to create a index. Now it is faster!
CREATE INDEX default ON default(EmailAddress)

6

I am facing a similar performance issue with the CouchBase 3.0 and N1QL.

I used the beer-sample database which contains 7000 records. I created an index on the beer-sample database using N1QL.

CREATE PRIMARY INDEX ON beer-sample

I tried querying the records from the CouchBase using the simple N1QL query -

SELECT * FROM beer-sample

But it took 4 seconds to fetch the 7000 records.

When I tried to retrieve the same 7000 records by creating a View on the beer-sample bucket, it came lightning fast in 500ms.

I don’t understnad what is causing this difference. Is it because N1QL is not yet optimized or I am doing something wrong?

-Kris

7

Hi Kris,

Is your view emitting the entire document? Are you making both calls using the REST APIs, or via a client SDK?

FYI, we will be releasing N1QL DP4 in a couple of weeks. I performed the same test using N1QL DP4 and the cbq command-line shell, all running on my laptop; it takes about 1 second.

Thanks.

8

Thanks for the quick reply. I performed the same test using N1QL DP3 and the cbq command-line shell using .NET SDK, all running on my laptop. I allocated 2GB of RAM for the beer-sample bucket.

9

Ok. Please let us know what performance you see in DP4, and how it compares to views that return equivalent results. Thanks.

10

Thanks. Waiting for DP4 to arrive.

11

Arrived: http://blog.couchbase.com/n1ql-dp4-is-here