DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Performance Enhancements to N1QL in the Couchbase 5.0 March Developer Build
Content provided by Couchbase logo

Performance Enhancements to N1QL in the Couchbase 5.0 March Developer Build

Take a look at what specifically was done to enhance myriad areas in Couchbase and how can we make use of these changes.

Nic Raboy user avatar by
Nic Raboy
·
Apr. 23, 17 · News
Like (2)
Save
Tweet
Share
2.19K Views

N1QL in Couchbase has come a long way since it was first introduced in Couchbase Server 4.0. In Couchbase 5.0, things are taken to the next level in terms of performance. In terms of the March 2017 Developer build of Couchbase 5.0, there are performance enhancements to N1QL in the flavor of index projection, enhancements to COUNT and DISTINCT, and the much requested ORDER BY, LIMIT, and OFFSET operators.

So, what specifically was done to enhance all of these areas and how can we make use of the changes?

Let’s take index projection, for example. When creating an index, you can create one with any number of properties. For example, take the following index:

CREATE INDEX idx ON default(type, firstname, lastname);

The above statement will create a covering index on the default Bucket for the type, firstname, and lastname properties of any given document.

Now let’s say we created the following N1QL query to retrieve a few documents with the idx index we had created:

SELECT firstname
FROM default
WHERE type = 'person'

The above query would use the idx index and return only the firstname property for every document that matches. The concept of querying this way is nothing new, however, what happens behind the scenes has changed. You’ll notice that even though our index has many keys, we’re only interested in a subset, or in this case two keys.

So, what is happening and why is this important?

In previous versions of Couchbase, all keys of the index were taken into consideration regardless if only a subset were used. As a result, more network, CPU, and memory were needed to accommodate what was happening. Now, this is not the case.

So, how do you know index projection is happening?

Do an EXPLAIN on the query that you’re running:

EXPLAIN SELECT firstname
FROM default
WHERE type = 'person'

In the results, you should see something regarding index_projection that looks like the following:

...
"index_projection": {
    "entry_keys": [
        0,
        1
    ]
},
...

The entry_keys property will change based on your query. For example, what if we add one WHERE condition like so?

SELECT firstname
FROM default
WHERE type = 'person' AND lastname = 'Nic'

In the above scenario, we would get an EXPLAIN result that looks like the following:

...
"index_projection": {
    "entry_keys": [
        0,
        1,
        2
    ]
},
...

Now, the above query wasn’t an index projection because we used all keys in our covering index.

Creating proper indexes paired with index projection can really help in overall performance and scaling your Couchbase Server cluster.

Index projection wasn’t the only performance enhancement made in the March 2017 build right? That is correct, there is more!

Let’s take the COUNT(DISTINCT) operation, for example. Now, let’s use that operation in the following query:

EXPLAIN SELECT COUNT(DISTINCT type)
FROM default;

In the results, you’ll notice that it is using IndexCountDistinctScan2 and is storing all type in the index and processing the distinct values. While it happens in the indexer in Couchbase 5.0, it previously happened in the N1QL service in prior editions. By offloading this operation in the indexer, we can experience significant performance gains.

Similarly, take the OFFSET, LIMIT, and ORDER BY operators that can be used in N1QL queries. Take the following query, for example:

EXPLAIN SELECT firstname
FROM default
WHERE type = 'person'
ORDER BY firstname
LIMIT 1
OFFSET 1;

You’ll notice that the LIMIT, ORDER BY, and OFFSET operators will appear in the indexer. Prior to 5.0, the LIMIT operator appeared in the indexer, but now the others do as well. This is a huge win because in previous versions of Couchbase if you were to offset the results, N1QL would get all X number of results, and drop everything before the offset.


Comments

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: