Prepared query in nodejs


#1

@brett19 According to https://docs.couchbase.com/nodejs-sdk/2.6/n1ql-query.html#prepare-stmts we must set adhoc to false to indicate that an SDK should optimize a query

var query_string = 'SELECT airportname FROM `travel-sample` WHERE country=$1';
var query = couchbase.N1qlQuery.fromString(query_string).adhoc(false);

I want to know If I have two same couchbase.N1qlQuery.fromString object like

var query_string = 'SELECT airportname FROM `travel-sample` WHERE country=$1';
var query1 = couchbase.N1qlQuery.fromString(query_string).adhoc(false);
var query2 = couchbase.N1qlQuery.fromString(query_string).adhoc(false);

What happens? each query that their own cached query plan? or SDK handle it , I ask it because I want to know in a function that I want to call it millions , inside function I have couchbase.N1qlQuery.fromString must I use global variable to keep a single query or not


#2

Hey @socketman2016,

The queries are hashed for the query cache and additionally, the query itself is verified at execution time (such that collisions would potentially cause a recache, but never an incorrect query execution, this obviously would be nearly impossible anyways). Utilizing the same query object or different query objects will no matter, as the cache is global to a particular bucket instance inside your application.

Cheers, Brett


#3

@brett19 , So how we can clear cache?


#4

@brett19

Do not turn off the adhoc flag for each query since only a finite number of query plans (currently 5000) can be stored in the SDK.

same query object or different query objects will no matter

is different query objects with same query counts in 5000 limits? or not?


#5

@brett19 I can run a query in nodeJS SDK but when I add .adhoc(false) , I have the following error

No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.

I have optimized index and it is online just problem is .adhoc(false)


#6

@brett19 is it a bug?


#7

Hey @socketman2016,

You can clear the cache using the Bucket.invalidateQueryCache method. The adhoc=false flag causes your prepared queries to be prepared early using the original indexes that are available, and new indexes that are created will not be taken into account. You may wish to ensure that invalidateQueryCache is invoke at some point to clear out the cache of any old entries if you are dynamically creating your indices. Additionally, it’s possible that your indexes are online but the query engine is not matching your index to the query, would you be able to elaborate a bit on which indexes you have available and what queries you are performing?

Cheers, Brett


#8

@brett19 thanks for your response
The query itself is not important here, and I’m sure I have correct index and it’s online, as I explain it and it works without prepared

I want to know , where is cache storage? Is it in memory? Or hard disk?