Prepared Statement for Couchbase as an Alternative For RDBMS Stored Procedures
Hi everyone,
In this article, I will try to explain “PREPARED STATEMENTS” on Couchbase, which is an alternative for RDBMS “STORED PROCEDURES”. I hope you all enjoy and maybe solve your problems depend on this :)
WHAT IS STORED PROCEDURE?
As you know when it comes to run the query on RDBMS its important to use SP(Stored Procedures)’s. Why is it important so?
Because when you run “ad-hoc” queries it goes to query engine. After some process query engine decides to use most efficient and costless query plan. This is important guys, for every single “ad-hoc” query, query engine does this. What is the problem here then?
Problem is preparing a “query plan” then choose it for “execution plan” increases your execution time for every single “ad-hoc” query. This is a problem for almost every database environment.
In RDBMS this problem is solved by SP’s(It could have different name for same purposes). When you create a “SP” it creates a query plan and keep it in cache. Whenever you call your “SP” it uses the same plan that you created in the beginning. It doesn’t create query plans again and again. So you can speed up your queries by just doing this. And you have recompile option whenever it loses its efficiency.
WHAT IS COUCHBASE PREPARED STATEMENTS?
We can say Couchbase “PREPARED STATEMENTS” is alternative for SP’s but its high available and made for Couchbase only :)
Sometimes building plans for N1QL requests may be expensive. Especially if a cluster has many indexes. Sometimes planning may take more time than executing a statement. “PREPARED STATEMENT” feature is developed by Couchbase to avoid this.
If you know that a statement will be executed repeatedly its perfect match to use “PREPARED STATEMENTS”. Because Couchbase has the N1QL query engine and everytime you send a N1QL request, it builds a plan to use it for execution. “PREPARED STATEMENTS” builds your plan, caches it and makes you use it as many times as you need, thereby avoiding the cost of repeated planning. By “FORCE” option you can recompile your query plan and keep it efficient.
Prepared statements are stored in the prepared statement cache until you restart the Couchbase Server. In Couchbase Server 6.5 and later, the query engine uses the prepared statement cache to speed up the creation of prepared statements. Whenever a “PREPARED STATEMENT” created statements are distributed to all query nodes.
In Couchbase Server 6.5 and later, when a query node is started or restarted, the prepared statement cache is primed from another node. If it is not possible to prime the statement cache from another node, you must prepare the statements again before you can execute them.
HOW TO USE?
PREPARED STATEMENT usage is extremely simple. Write your statement and add “PREPARE <PSname> AS/FROM” before your statement. Let me show you an example for “SELECT” statement.
PREPARE examplestatement AS
SELECT * FROM `travel-sample` WHERE type=”hotel” AND city=$1 AND country=$2;#Execute the PREPARED STATEMENT
EXECUTE examplestatement USING [“paris”, “france”];or
EXECUTE examplestatement USING {“city”:”paris”, “country”:”france”};
You can find nother example for “UPDATE” statements below.
PREPARE examplestatement AS
UPDATE `travel-sample` SET city = “San Francisco”
WHERE lower(city) = $1
RETURNING *#Execute the PREPARED STATEMENT
EXECUTE examplestatement USING [“paris”];or
EXECUTE examplestatement USING {“city”:”paris”};
In addition to this setting if you use “adhoc=false” in your SDK configurations, it will follow the same path as “PREPARED STATEMENTS” app server memory will cache your query execution plan. It will reuse cached query plan and will not try to build another query plan.
COMPARISON AD-HOC vs PREPARED
In this part i will test the “PREPARED STATEMENT” performance against “adhoc” query and i used “cbq” cli for this.
- Firstly i ran an “ad-hoc” query in the comments section and get the results in the first picture.
- Then i PREPARED this statement as you can see in the comments section at the second picture.
- Finally i ran the “EXECUTE test;” to use the “PREPARED STATEMENT” in the comments section and i got the results in second picture.
If we compare this 2 pictures. We can clearly see “plan” time is significantly decreased. Even though this statement takes just takes “6ms” to execute. Other statistics changed too.
But it’s not an effect of “PREPARED STATEMENT” those may change in every execution.
As a result of this test we can say if the planning takes some time to build you can handle this situation by using “PREPARED STATEMENT” feature.
CONCLUSION
If you are running your queries over and over. This solution can make you run faster executions. Thereby you can increase your QPS(Query per Second) or you can solve your slow querying issue.
Also this feature shows you some statistics, which are highest total execution times, highest execution counts , which nodes are used for executions and how many times got they used etc. These statistics make your executions and query usage more visible to everyone.
My main approach is “No need to have a problem you can always make some things better”. I hope you enjoyed and found this article useful :)
I will continue to write articles about Couchbase, SQL Server and Postgresql as soon as possible. I like to share my knowledge and make improvements in the environments work.
Keep Following guys :)
Ahmet Demir
