With NoSQL databases proving their worth over the legacy SQL approach, the next question is inevitably “how do we optimize our processes as much as possible?” Many databases have used static Rule-Based Optimization (RBO) to optimize workloads – but this has its limits. Cost-Based Optimization (CBO) allows for much more agile, and automated, optimization – using the fewest possible resources. In this light, Couchbase introduced its Cost-Based Optimizer for JSON as a preview in Couchbase Server 6.5, and we’re delighted to announce that our Cost-Based Optimization technology is patent pending.
So why choose CBO over RBO? Picture the scene: you’re running late for a meeting on the other side of town. You check Google Maps and find what looks like, to the untrained eye, the fastest possible route there. But when you get underway, you soon find that the route is taking you straight into heavy traffic, road closures, and at certain points, even the wrong way along a one-way street.
Naturally, you’d expect an app like Google Maps to take factors that affect your journey into account, instead of automatically just choosing the route covering the shortest possible distance. You’d expect it to steer you clear of heavy traffic, and display a dynamic route suggestion based on every factor that could impact your journey. You wouldn’t expect anything less than the most efficient journey possible – whether that means the shortest journey time, least fuel used, or lowest cost from fares or tolls.
CBO performs the same role for database workloads. RBO must always follow the same rigid rules – for instance, “give me the shortest possible journey” would produce the first scenario above. In comparison, CBO takes metadata and statistics to select which query-execution plan will use the least possible resources, such as memory, CPU or I/O – or, in our example above, time. Ultimately, a smart CBO will always give you the most cost-effective option, avoiding potential obstacles.
Couchbase’s Cost-Based Optimizer for JSON delivers reduced query latency, while making the developer’s job easier. CBO is available as a complementary feature to the existing Rule Based Optimization, improving query efficiency for large numbers of cases. It achieves this through better index selection than its RBO counterpart, along with better JOIN type selection, and better ordering of the processing itself – all of which affect query latency. In essence, developers will simply write the query, and let the CBO determine the best way forward.
To find out more about our CBO for JSON, take a look at some of our other blogs:
Less is More: Why Cost Based Optimizer?
Cost Based Optimizer for Couchbase N1QL (SQL for JSON)
Other reference material:
Access Path Selection in a Relational Database Management System
English