CODEWITHSUNDEEP
azurestored-procedurestimeoutazure-sql-databaseazure-cosmosdb
TheDeveloper
TheDeveloper

Reputation: 1217

DocumentDB: How to run a query without timing out

I am new to the documentDb. I wrote a stored procedure that checks all records and update them under certain circumstances.

Current scenario:

It would run 100 records at a time, updates them and after running few times( taking 100 records at a time and updating) it is timing out.

Expectation

Run the script on all the records without timing out.

The document has close to a million records. So, running the same script multiple times manually is not a the way I am looking for.

Can anyone please advise me how I can achieve that?

Upvotes: 1

Views: 1233

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9523

tl;dr; Keep calling the sproc with the query continuation token being passed back and forth.

A few thoughts:

  1. There is no capacity of RUs for collections that will allow you to do all million in one call to the sproc.

  2. Sprocs run in isolation on a single replica. This means that they can be transactional but their use will have lower throughput than a regular query that can use all replicas to satisfy the request, so unless you need it to be in a sproc, I recommend using direct queries for reads that don't need to be transactional with writes. Even then, with a million documents, your queries will max out and you'll have to run the query again with a continuation token.

  3. If you must use a sproc... As you are probably aware since you have done the 100 at a time thing, each query returns a continuation token. You can actually add that to the package that you send back from your sproc when it times out. Then you can pass that back into another call to the same sproc and write your sproc to pick up where you left off. The documentdb-utils library for node.js automatically re-calls the sproc until done as long as you follow this pattern for writing your sprocs. If you are using node.js, you could use that (but it has not yet been upgraded to support partitioned collections) or you could write the equivalent in whatever platform you are using.

Upvotes: 1

Related Questions