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'

@Jacobm001type is not indexed yet, but will be at a later date. — pookie, Jul 18 '17 at 15:39
@GordonLinoff I have tested when the very first (or at least in the top 10) record matches. I'm really just interested in how the select TOP works. — pookie, Jul 18 '17 at 15:40
@pookie Just out of curiosity, did you specifically opt out of indexing the `type` property? By default all fields in Cosmos are indexed and you have to go out of your way to turn off this behavior for specific JSON paths. — Jesse Carter, Jul 18 '17 at 16:02
@pookie If you haven't adjusted the indexing policies then type is definitely indexed and the accepted answer is not correct — Jesse Carter, Jul 19 '17 at 12:40
@JesseCarter Thanks, but if the answer I marked is not correct and you know the correct answer, could you please post your answer? — pookie, Jul 20 '17 at 09:57
While running the top query getting "Object reference not set to an instance of an object". Did Top work for you ? — Arpan Saini, Dec 11 '19 at 19:55
@pookie : it seems Cosmos db Explorer issue, Top worked for me in SDK call, between thanks for the response — Arpan Saini, Dec 12 '19 at 22:08

score 22 · Answer 1 · edited Jan 03 '20 at 15:24

22

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://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit

edited Jan 03 '20 at 15:24

Ṃųỻịgǻňạcểơửṩ

2,517
8
22
33

answered Jan 03 '20 at 15:03

Arjun Bhalodiya

293
2
14

Jacobm001 · Answer 2 · 2019-12-10T19:04:05.847

1

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.

edited Dec 10 '19 at 19:04

answered Jul 18 '17 at 15:58

Jacobm001

4,431
4
30
51

Great, thanks, it makes sense that it takes the first record and then stops searching, but I was not convinced it was doing that. Thanks for your help. – pookie Jul 18 '17 at 16:17
Apparently, CosmosDB indexes everything by default. Since I've not touched any indexing policies, c.type _is_ indexed and therefore your second paragraph is not correct. I've unmarked it as the solution so if you can, please update your answer so that it can be considered again. Thanks! – pookie Jul 20 '17 at 10:00

score 1 · Answer 3 · answered Dec 12 '19 at 22:13

1

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://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery

answered Dec 12 '19 at 22:13

Arpan Saini

4,623
1
42
50

1

Now there is a "New SQL Query" button to open an editor, which can run a complete query. – themefield Jul 21 '21 at 16:15

CosmosDB: How does SELECT TOP work?

3 Answers3