Profiling N1QL Queries


#1

I am in the final stages of evaluating Couch Base as the preferred data store for financial markets data.
I have noticed if the query returns large amount of documents the executing time reported by the Couch Base UI increases a lot BUT may not be representative of how long the query took to run. For example this query
SELECT t.tradeData.id
FROM DartAggregatedComplete t
WHERE t.ddasSnapshotVersion.asOfDate BETWEEN “2018-01-01T00:00:00” AND “2018-02-01T00:00:00”

LIMIT 1000

Runs in 200 ms. My question is if the LIMIT is removed the User Interface reports after a while there is too much data to process.
I think the query has to run to completion before the LIMIT is applied.

Would this be correct?

  1. By placing the LIMIT, the query still runs to completion and extra time taken without the limit has got nothing to do with how long the query took to run BUT rather the time to buffer and display it on the client side.
  2. Rather silly question, but can I have a SELECT … FROM but display no data so I can get the actual query execution time.

I’m sorry for the rather trivial questions being asked, but I need to profile the query times to use Couch base as the preferred data store .

Regards,

John


#2

If there is no LIMIT query needs to produce all the qualified results to so it runs till completion.
If there is OFFSET/LIMIT those called pagination queries. Whenever query produces desired results it stops (query optimization) doesn’t required to produce complete results.
If there is ORDER BY, LIMIT/OFFSET and query ORDER BY follows index key order, whenever query produces desired results it stops (query optimization) . If doesn’t follow index order it still needs produce complete results but sort can be done top-N sort.

You can use cbq shell or REST API instead of query workbench no limit on data.
OR SELECT COUNT(*) FROM (query you are running) AS d