How I can calculate Query time and Query Execution plan in Couchbase.Is there any Utilities like Oracle Explain plan and tkprof in Couchbase db?
edit:
I am trying to see which database performs best for my data. So i am trying to experiment with mysql, mongodb, couchbase. I have tried with three different number of entries 10k, 20k, 40k entries.
With mysql, i can see the query time using "set profiling =1". with this settings i ran queries under three scenarios 1) without indexing primary key, 2) after indexing primary key 3) running the same query second time ( to see the effect of query caching)
Similarly i ran same tests with mongodb and summarized my results in a table format. I would want to run same tests with couchbase to see how well it would perform. I tried to search over net but couldn't find anything which i can follow to get similar results.
Below is the table i have (all times are in milli seconds). the second row with braces() shows the query time for second run.
Records Count Mysql MongoDB CouchBase ___________________ _______________ ___________ Without | With Without | With With Index Index | Index Index | Index 10K 62.27325 | 8.537 3311 | 33 (33.3135) | (3.27825) (7) | (0) 20K 108.4075 | 23.238 132 | 39 (80.90525)| (4.576) (17) | (0) 40K 155.074 | 26.26725 48 | 10 (110.42) | (10.037) (42) | (0)
For couchbase i would want to know both the performance when retrieving a document using its key( similar function as memcahed). Also the query time using its views.
You have to understand that couchbase works differently to RDBMS's such as Oracle. Couchbase offers two ways for you to retrieve your data:
1) Key lookup, you know the key(s) of the document(s) that you want to retrieve.
2) Define Map Reduce jobs called Views which create indexes allowing you to query your data on attributes other than the key.
Couchbase documents are always consistent but views are not and are eventually consistent (although you have the ability to change this).
As the couchbase documentation states
So query time really depends on a variety of factors, can the view data be stale? How large is the data emitted from the index, and what is the current workload and db size? Couchbase provides the following 3 flags for working with views and how you want to access the data. False means the index has to be updated before returning the result, therefore it can potentially be slow.
Please check out the official document for more in depth answers http://docs.couchbase.com/couchbase-manual-2.2/#views-and-indexes
Also you can check out this interesting article on caching views http://blog.couchbase.com/caching-queries-couchbase-high-performance
Currently in development at Couchbase is N1QL, effectively the couchbase version of SQL, this will have the EXPLAIN statement available, this won't be released until late 2014 I believe.
A blog post introducing N1QL http://blog.couchbase.com/n1ql-it-makes-cents
A cheat sheet for N1QL http://www.couchbase.com/communities/sites/default/files/Couchbase-N1QL-CheatSheet.pdf
And where you can download the dev preview if you want to play with N1QL http://www.couchbase.com/communities/n1ql
Also checkout the cb stats tool http://docs.couchbase.com/couchbase-manual-2.2/#cbstats-tool it gives a high level overview of persistence rates,updates,key misses etc.