CosmosDB: How does SELECT TOP work?

Question

I have a 450GB database... with millions of records.

Here is an example query:

SELECT TOP 1 * FROM c WHERE c.type='water';

To speed up our queries, I thought about just taking the first one but we have noticed that the query still takes quite a while, despite the very first record in the database matching our constraints.

So, my question is, how does the SELECT TOP 1 really work? Does it:

A) Select ALL records and then return just the first (top) one where type='water'

B) Return the first record which is encountered where type='water'

Answer 1

Try this line, noting the offset limit:

SELECT * FROM c WHERE c.type='water' OFFSET 0 LIMIT 1

For more information about the offset limit:
https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit

Answer 2

Cosmos db Explorer doesn't work with the TOP Command, It's an existing issue. It works fine in SDK Call.

Check some Top command usage below

https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery

Answer 3

Assuming you aren't sorting your results (which you query isn't) then TOP 1 will return the first result as soon as it finds one. This should then end the query.