Details
Description
I have the following script. It requires the travel-sample bucket:
#!/usr/bin/python
|
from couchbase.bucket import Bucket |
from couchbase.n1ql import N1QLQuery |
|
cb = Bucket('couchbase://localhost/travel-sample') |
|
def run_query(): |
for x in range(10): |
q = N1QLQuery('select title from `travel-sample` where city is not missing limit 10;') |
q.adhoc = False |
for row in cb.n1ql_query(q): |
print row |
|
run_query()
|
again = raw_input('Run the curl command then hit enter to run again ') |
run_query()
|
After I let it run to the raw_input() I checked the output of select * from system:prepareds; and I notice that the uses count is zero. This suggests that the prepared statement is not being used:
cbq> select * from system:prepareds;
|
{
|
"requestID": "4e35a5b2-194d-47fd-a691-485792e93ffe",
|
"signature": {
|
"*": "*"
|
},
|
"results": [
|
{
|
"prepareds": {
|
"encoded_plan": "H4sIAAAJbogA/5ySQW/bMAyF/4rAXVrAHuysyZbs1EMLBOghSI5DkKgS5aiTJU+S23SF99tHuc2KOMMw5GSZenjk+6gXQCucRLlpDLcwA8jA8hrppKZqrESh8tFoIvOrQoh8Ov08ze+FLMX4ivN79YnUrkHPo/Mwe4EP7z+wwh8teSNJfomdNtIj+X87Fl23cee8/plUvegsl/8Sza3E/UpQyAx0OlNNotoIHZ8PpY2mAeDLeCLVRI3kZMwLXqSQ3/E5NFwkLNHzRzR54HVjUkejax2pXhZv6A5CMuetiVSlig39QEtuK0wZ51aYNmhHc5YZ3LknugbbGgPrrltnQHe2IpMqaOiy4yQL7rkxaP7EPQvaLUax+3e0v6UZDnOrTUSf1ues1LFPBBcX2yOv7cdtwry9ZDow6yKrdUgBL0/s5pY8uFl494AisfMYqO2GetSvCHHfJOFph6gjfclyfTqjfbfsEuCh4q5fYnYwp10mDTReP2qDFYaE+A3B7BjTrOyGbqvokdevFkFXlsfW91vvRyTBQyBMdBlxn57OYnmzuF7esICGJmS9iinvajYIyZ526JEllgOSrH+GrCy+Qvc7AAD//zgs5fjUAwAA",
|
"name": "f9f5fc0f-226d-40cc-9979-bcd1c54aabf3",
|
"statement": "PREPARE select title from `travel-sample` where city is not missing limit 10;",
|
"uses": 0
|
}
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "587.218µs",
|
"executionTime": "564.711µs",
|
"resultCount": 1,
|
"resultSize": 904
|
}
|
}
|
If I run the follow curl command then the system:prepared command again we can see the count increase to one:
curl -Ss http://localhost:8093/query/service -d 'prepared="f9f5fc0f-226d-40cc-9979-bcd1c54aabf3"'
|
system:prepareds:
cbq> select * from system:prepareds;
|
{
|
"requestID": "bfc34ca0-e431-416b-b563-0d563b47cda9",
|
"signature": {
|
"*": "*"
|
},
|
"results": [
|
{
|
"prepareds": {
|
"avgElapsedTime": "4.327031ms",
|
"avgServiceTime": "4.225616ms",
|
"encoded_plan": "H4sIAAAJbogA/5ySQW/bMAyF/4rAXVrAHuysyZbs1EMLBOghSI5DkKgS5aiTJU+S23SF99tHuc2KOMMw5GSZenjk+6gXQCucRLlpDLcwA8jA8hrppKZqrESh8tFoIvOrQoh8Ov08ze+FLMX4ivN79YnUrkHPo/Mwe4EP7z+wwh8teSNJfomdNtIj+X87Fl23cee8/plUvegsl/8Sza3E/UpQyAx0OlNNotoIHZ8PpY2mAeDLeCLVRI3kZMwLXqSQ3/E5NFwkLNHzRzR54HVjUkejax2pXhZv6A5CMuetiVSlig39QEtuK0wZ51aYNmhHc5YZ3LknugbbGgPrrltnQHe2IpMqaOiy4yQL7rkxaP7EPQvaLUax+3e0v6UZDnOrTUSf1ues1LFPBBcX2yOv7cdtwry9ZDow6yKrdUgBL0/s5pY8uFl494AisfMYqO2GetSvCHHfJOFph6gjfclyfTqjfbfsEuCh4q5fYnYwp10mDTReP2qDFYaE+A3B7BjTrOyGbqvokdevFkFXlsfW91vvRyTBQyBMdBlxn57OYnmzuF7esICGJmS9iinvajYIyZ526JEllgOSrH+GrCy+Qvc7AAD//zgs5fjUAwAA",
|
"lastUse": "2016-07-09 11:40:54.279956607 +0100 BST",
|
"name": "f9f5fc0f-226d-40cc-9979-bcd1c54aabf3",
|
"statement": "PREPARE select title from `travel-sample` where city is not missing limit 10;",
|
"uses": 1
|
}
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "904.34µs",
|
"executionTime": "871.435µs",
|
"resultCount": 1,
|
"resultSize": 1070
|
}
|
}
|
We can see that it increased to 1, if we now hit enter then check system:prepared once more we now see that uses is zero:
cbq> select * from system:prepareds;
|
{
|
"requestID": "a5671bb7-f62e-4c04-b798-964ebca9f32a",
|
"signature": {
|
"*": "*"
|
},
|
"results": [
|
{
|
"prepareds": {
|
"encoded_plan": "H4sIAAAJbogA/5ySQW/bMAyF/4rAXVrAHuysyZbs1EMLBOghSI5DkKgS5aiTJU+S23SF99tHuc2KOMMw5GSZenjk+6gXQCucRLlpDLcwA8jA8hrppKZqrESh8tFoIvOrQoh8Ov08ze+FLMX4ivN79YnUrkHPo/Mwe4EP7z+wwh8teSNJfomdNtIj+X87Fl23cee8/plUvegsl/8Sza3E/UpQyAx0OlNNotoIHZ8PpY2mAeDLeCLVRI3kZMwLXqSQ3/E5NFwkLNHzRzR54HVjUkejax2pXhZv6A5CMuetiVSlig39QEtuK0wZ51aYNmhHc5YZ3LknugbbGgPrrltnQHe2IpMqaOiy4yQL7rkxaP7EPQvaLUax+3e0v6UZDnOrTUSf1ues1LFPBBcX2yOv7cdtwry9ZDow6yKrdUgBL0/s5pY8uFl494AisfMYqO2GetSvCHHfJOFph6gjfclyfTqjfbfsEuCh4q5fYnYwp10mDTReP2qDFYaE+A3B7BjTrOyGbqvokdevFkFXlsfW91vvRyTBQyBMdBlxn57OYnmzuF7esICGJmS9iinvajYIyZ526JEllgOSrH+GrCy+Qvc7AAD//zgs5fjUAwAA",
|
"name": "f9f5fc0f-226d-40cc-9979-bcd1c54aabf3",
|
"statement": "PREPARE select title from `travel-sample` where city is not missing limit 10;",
|
"uses": 0
|
}
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "734.339µs",
|
"executionTime": "673.315µs",
|
"resultCount": 1,
|
"resultSize": 904
|
}
|
}
|
If this script is wrong, then I will argue that this documentation needs to improve. I suspect that the SDK just keeps creating the same prepared statement over and over again but never uses it.
Attachments
Issue Links
- duplicates
-
MB-19509 query with encoded_plan erroneously sets wrong plan in prepareds cache
- Closed