We have a bucket in our Production Couchbase Server having 23K documents.
But, the total documents including sync documents is around 2.4 million.
First we were using this query to fetch all the 23K documents.
1)
SELECT
A.externalPersonId AS StudentId,
A.balance AS AccountBalance
FROM
POS_Account A
WHERE
A.type = 'Account'
AND A.regionId = 23
AND A.isStudent = true
AND META(A).id NOT LIKE '_sync%'
We started seeing slowness after some time and we went ahead and modified the query as shown below.
2)
SELECT
A.externalPersonId AS StudentId,
A.balance AS AccountBalance
FROM
POS_Account A
WHERE
A.type = 'Account'
AND A.regionId = {0}
AND A.isStudent = true
AND META(A).id = CONCAT('Account_', A.accountId)
Now again after sometime ,we started seeing slowness with the above query (#2).
It is taking around 3 mins 48 seconds,
We added all the required indexes that came up when we used Index Recommedation, but with no success.
We went ahead and tried the query #1 and we are seeing that the query #1 is now running much faster (taking 5-10 seconds) compared to query #2.
We are not sure what is causing these 2 queries to slow down after some period of time.
We have to constantly keep updating this query for it to work in our Production Environment.
Please can we get some insight from Couchbase on how to alleviate this issue permanantly?
Also, we tried to re-produce this issue in our QA environment by adding 23K records, but both these 2 queries are getting completed withing 5-10 seconds.
The only diffierence between our Production and QA environment is that in QA we dont have as many sync documents as we have it in Production.
We are seeing this slowness due to the presence of large volumne of sync documents.
So far ,we have 132 sync documents for each document and we are afraid that these will keep on increasing as we add more number of documents to this bucket.
Please let us know if there is a way where in the old sync documents can be purged?
Please feel free to ask if any additional information is needed.