DocumentDB Extreme Query Cost using IS_DEFINED

Question

I've had to expand a query to support backwards compatibility with a property that may not exist in older documents, to do this, I've used the IS_DEFINED method to check if the boolean field exists on the object.

I've just noticed that by using this the RU cost goes up 15000%

SELECT T.id FROM Panels T WHERE T.customerId = @CustomerId

COST: ~3 RU

SELECT T.id FROM Panels T WHERE T.customerId = @CustomerId AND (T.archived = false)

COST: ~4 RU

SELECT T.id FROM Panels T WHERE T.customerId = @CustomerId AND (NOT IS_DEFINED(T.archived) OR T.archived = false)

COST: ~450 RU

That is a pretty MASSIVE difference for a boolean field, and really hammering our throughput.

UPDATE: Found a related issue here - DocumentDB Query Requires Unexpected High RUs

Looks like NOT is an issue, so would need some kind of logic like:

SELECT T.id FROM Panels T WHERE T.customerId = @CustomerId AND IIF(IS_DEFINED(T.archived), T.archived, false) = false

But I guess that would still involve a scan?

UPDATE 2:

Tried running the operation without the NOT, and it came in at ~750, but the it had a result set of 1500 id fields.

Tristan - please stop adding 'thanks', explicit commentary such as 'please advise' and mentions of needing it ASAP. This is completely unneeded and should be removed. I already removed it once, and you re-added it. It's superfluous and, in the context of StackOverflow, completely unneeded. Further: StackOverflow is a volunteer-run site, so mentioning urgency doesn't go over well. — David Makogon
I haven't touched the post except to add the UPDATE and UPDATE 2 parts. — Tristan Rhodes
Can you please email [email protected] with the endpoint and activity IDs for the queries, so that we can investigate? Not Is_Defined cannot use the index, but the query can still use the CustomerID index. — Aravind Krishna R.

Aravind Krishna R. Aravind Krishna R. · Accepted Answer · 2017-04-11T16:56:23

The filter on T.customerId = @CustomerId will allow the query to use the index on customerId, but it cannot use the index on archived. A query filter using NOT does not use the index in DocumentDB (like in any database). A scan will result in a higher number of RUs consumed.

That said, specific queries might have different execution plans. Since DocumentDB is a managed service, you can have the DocumentDB team debug any performance issues by filing an Azure support ticket, or by emailing [email protected].

DocumentDB Extreme Query Cost using IS_DEFINED

1 Answers