Azure-cosmos-dotnet-v3: ExecuteStoredProcedureAsync without partitionKey

[ravindraaditya]

My collection has partitionkey defined and inserted records in to it. however, while executing store procedure it is expecting partitionkey though i' need to query entire collection (eg:- SELECT * FROM root c)

Can we run script without partitionkey while calling ExecuteStoredProcedureAsync in V3 ?

What do you recommend? What are the alternatives? need to fallback to V2?

Thankyou.

[bartelink]

Stored procs don't run across logical partions in CosmosDB period - v2 has nothing to do with it. Bottom line is there arent going to be cross-server scatter-gather calls taking place over there. Perhaps talk more about your actual scenario so an appropriate alternate approach can be suggested?

[ravindraaditya]

I agree.

Well, my ask is simple. How do we call stored procedure without partitionkey (As I don't have partitionkey handy) using V3?

To be more specific,

We are migrating from v2 to v3 SDK. we are trying to understand how the following stored procedure query execute without partitionkey input using V3 methods.

SELECT c.id FROM c where c.nonpartitionkey = 'hello'

SELECT c.id FROM c JOIN p in c.profiles where p.nonpartitionkey = 'test'

This is working with ExecuteStoredProcedureAsync using V2 fine however, for V3 there is a dependency with partitionkey in the methods.

I'm referring this sample:
https://github.com/Azure/azure-cosmos-dotnet-v3/blob/master/Microsoft.Azure.Cosmos.Samples/CodeSamples/ServerSideScripts/Program.cs#L135-L138

How do we do that?

Apologies for any confusion here. this is blocking us to move to V3. hope my ask is clear.

[ravindraaditya]

Any help.?

[j82w]

Hi @ravindraaditya,

Is this what you are trying to do? Does the container have a partition key?

            StoredProcedureExecuteResponse<string> response = await container.Scripts.ExecuteStoredProcedureAsync<string>(
                scriptId,
                PartitionKey.None,
                new dynamic[] { "Hello" });

[ravindraaditya]

Hi @j82w,

with PartitionKey.None we get no results.

[j82w]

1. Does the container have a partition key?
2. Did you create the container in v2 or v3?
3. Is there any way you can provide a simple console app with a repo?

[ravindraaditya]

@j82w The sample code is already available here. https://github.com/Azure/azure-cosmos-dotnet-v3/blob/master/Microsoft.Azure.Cosmos.Samples/CodeSamples/ServerSideScripts/Program.cs#L135-L138

Please help yourself and let me know we can fix this?

[j82w]

The samples got moved. I'll try creating a repo sometime next week with the none value.

[ravindraaditya]

@j82w any help?

[j82w]

I created a test that proves that PartitonKey.None works for non-partitioned containers.

If the container has a partition key then a partition key value must be supplied.

[ravindraaditya]

I created a test that proves that PartitonKey.None works for non-partitioned containers.

If the container has a partition key then a partition key value must be supplied.

@j82w Thanks for your efforts. can we include a test for the following scenarios as well.

1) How do we run cross partition query in V3 on a partitioned container over a storedproc without knowing partition key. if we have partition key handy why would we go for cross partition?

Apologies but trying to understand this.

Please review this example:
https://github.com/Azure/azure-cosmos-dotnet-v3/blob/1aa72086c18ea22352052c25d8c3edb1524f24df/Microsoft.Azure.Cosmos.Samples/Usage/ServerSideScripts/Program.cs#L131

[j82w]

Based on all the documentation a query inside a stored procedure is always restricted to a single partition.

https://stackoverflow.com/questions/40764375/documentdb-stored-proc-cross-partition-query

https://stackoverflow.com/questions/50681280/is-it-possible-to-execute-cross-partitioned-query-inside-trigger

What scenario are you trying to solve by doing a cross partition query inside a stored procedure?

[ravindraaditya]

@j82w Thanks for your response.

we are migrating from non-partitioned containers to partitioned containers. how do we tweak these following queries.

SELECT c.id FROM c where c.nonpartitionkey = 'hello'
SELECT c.id FROM c JOIN p in c.profiles where p.nonpartitionkey = 'test'.

Do we need to fallback to client side cross partition query api's instead of storedproc?

[j82w]

There is a few different options depending on what you are attempting to accomplish with these stored procedures. Can you explain what the purpose of the stored procedures are?

1. If this is just to guarantee a unique key it is recommend using unique key constraints.
2. Choose a partition key value that keeps it to a single partition and avoids the cross partition query. This is the most efficient way to do queries, and would allow existing stored procedures to work.
3. Fallback to client side cross partition queries. The RUs and speed of the query might be impacted as the container is scaled up.

[killerjoe1990]

Im also running into the same problem - is there not an alternative to this question previously asked in this thread:

How do we run cross partition query in V3 on a partitioned container over a storedproc without knowing partition key.

The only way for me to get those partition key values is to get all the documents in advance which defeats the purpose of calling a StoredProcedure with a sql select query. I also have no way to retrieve all the different partition key ranges like in V2 which I could pass in the RequestOptions specifying the partitionKeyRangeId. I need an alternative in the V3 SDK because right now migrating to V3 without this will cause a huge performance hit fetching all the documents in advance to get those partition keys. At least in other methods like GetItemLinqQueryable I could get it around it not specifying partition key in the QueryRequestOptions if I didn't know it in advance or in situations where all the partition keys differ.

[bozola]

Like killerjoe1990 said.

Sometimes my front-end doesn't know what the value of the partition key is before hand. Sometimes it's a guid of the device requesting further stored information. sometimes it's a general user uuid wanting to see what devices they have, etc.

If I partition on the user's uuid, and the request comes in from the device, which may not know who the user uuid is, how is this system anyway even remotely useful?

While MS is trying to get me to tune my data by partitioning it, they're no giving me the tools to query it effectively?