Count query is not giving correct record count after deleting record using ttl value

Question

Hi,

We are setting ttl value to 1 second for a record. After deletion of that record when we are running a count query, but the result still contains the count of the deleted record. It is taking around 3-4 minutes to get the correct count.

Answer 1

Hi Debashis Jena •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting delay in count query result after setting TTL.

Could you please check the TTL which is set at container level and item level?

As by default, you can set time to live at the container level and override the value on a per-item basis.

Please note:

Deletion of expired items is a background task that consumes left-over Request Units, that is Request Units that haven't been consumed by user requests. Even after the TTL has expired, if the container is overloaded with requests and if there aren't enough RU's available, the data deletion is delayed. Data is deleted once there are enough RUs available to perform the delete operation. Though the data deletion is delayed, data is not returned by any queries (by any API) after the TTL has expired.

Hope this helps. Let us know if further queries.

Thanks

Answer 2

Hi Shakti,

Thanks for your response.

We are setting TTL value at item level. We haven't set any TTL value at container level. We are setting the TTL value on conditional basis, so we can't set TTL value at container level.

Also one question on this point:-"Though the data deletion is delayed, data is not returned by any queries (by any API) after the TTL has expired."
As it is mentioned that data should not be returned by any queries, then why count query is returning deleted record count?

Is there any alternatives for count query that will return correct record count instantly after deletion of record using TTL value?

Answer 3

Hi Debashis Jena •,

Thanks for your patience.

I have got the below reply from the internal team:

The issue here is the expired documents might not have been physically deleted for this period of time. TTL would hide such documents by checking their expiration whenever a document is loading (soft delete). However, this does not apply to index terms which won’t be updated until a document is physically deleted. The Count query here seems to have been pushed to the index and hence it did not observe the “soft delete’.

Microsoft Documentation team has been asked to add information to ease reader.

Thanks for reporting this with us,

Have a great day!

Answer 4

Hi Debashis Jena •,

Thanks for your patience.

I have got the below reply from the internal team for the workaround:

The only workaround here is to force the query not to push the aggregate to the index. The query will become much slower and way more expensive, though. To do so, they could simply replace the constant/property reference of the Count expression by an expression that requires evaluation but is guaranteed to not to evaluate to undefined. For instance, an expression like this one:

 

SELECT COUNT(IS_DEFINED(c.id)) AS count
FROM c		

Hope this helps. Thanks