Cosmos Db stored procedure RU charge is higher than equivalent query via sdk or portal?
0
Is it expected that a query executed via a stored proc has a higher RU charge (almost double), than that of the same query executed via the sdk or portal?
This test code shows both cases execute a query for a single document against partitionKey and document id.
Simple query executed via stored proc
Simple stored proc to test execution of a single document by id. As this is executed withing the scope of a single partition, it should result in a direct query against partition and doc id.
function testProc(id, props) {
var collection = getContext().getCollection();
var collectionLink = collection.getSelfLink();
var response = getContext().getResponse();
// Validate input.
if (!id) throw new Error("The id is undefined or null.");
if (!props) throw new Error("The update is undefined or null.");
tryQuery();
function tryQuery() {
var query = {query: "select * from root r where r.id = @id", parameters: [{name: "@id", value: id}]};
var isAccepted = collection.queryDocuments(collectionLink, query, function (err, documents, responseOptions) {
if (err) throw err;
if (documents.length == 1) {
// Update props
} else {
// Else a document with the given id does not exist..
throw new Error("Document not found.");
}
});
// If we hit execution bounds - throw an exception.
// This is highly unlikely given that this is a query by id; but is included to serve as an example for larger queries.
if (!isAccepted) {
throw new Error("The stored procedure timed out.");
}
}
}
Request Charge: 5.68 RU
Query direct in the portal
SELECT * FROM c where c.partitionKey = 'Tenant_8_u768f5c6b-ef6d-4cd9-8ad0-ae0c68eb9887@mail.com' and c.id = 'c12a3dbd-ad19-41d6-971b-79e0c7acec41'
Request Charge: 2.890 RUs
I can only assume according to the cosmos request units and throughput documentation that this discrepancy comes about due to the physical overhead of compute resources (cpu / memory) required in order to run the stored proc itself.
For a simple query to pull out a document by partition and id it seems rather high though.
azure azure-cosmosdb asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
add a comment |
0
Is it expected that a query executed via a stored proc has a higher RU charge (almost double), than that of the same query executed via the sdk or portal?
This test code shows both cases execute a query for a single document against partitionKey and document id.
Simple query executed via stored proc
Simple stored proc to test execution of a single document by id. As this is executed withing the scope of a single partition, it should result in a direct query against partition and doc id.
function testProc(id, props) {
var collection = getContext().getCollection();
var collectionLink = collection.getSelfLink();
var response = getContext().getResponse();
// Validate input.
if (!id) throw new Error("The id is undefined or null.");
if (!props) throw new Error("The update is undefined or null.");
tryQuery();
function tryQuery() {
var query = {query: "select * from root r where r.id = @id", parameters: [{name: "@id", value: id}]};
var isAccepted = collection.queryDocuments(collectionLink, query, function (err, documents, responseOptions) {
if (err) throw err;
if (documents.length == 1) {
// Update props
} else {
// Else a document with the given id does not exist..
throw new Error("Document not found.");
}
});
// If we hit execution bounds - throw an exception.
// This is highly unlikely given that this is a query by id; but is included to serve as an example for larger queries.
if (!isAccepted) {
throw new Error("The stored procedure timed out.");
}
}
}
Request Charge: 5.68 RU
Query direct in the portal
SELECT * FROM c where c.partitionKey = 'Tenant_8_u768f5c6b-ef6d-4cd9-8ad0-ae0c68eb9887@mail.com' and c.id = 'c12a3dbd-ad19-41d6-971b-79e0c7acec41'
Request Charge: 2.890 RUs
I can only assume according to the cosmos request units and throughput documentation that this discrepancy comes about due to the physical overhead of compute resources (cpu / memory) required in order to run the stored proc itself.
For a simple query to pull out a document by partition and id it seems rather high though.
azure azure-cosmosdb asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
Have you tried running a version of your stored procedure with no validation / error checking, to see if that is adding to your RU cost? Also - fyi since you know the ID you're searching for, a direct read (via SDK call) will cost less than the equivalent query.
– David Makogon
Dec 28 '18 at 13:44
As indicated this was a test I put together when I noticed something was off with with a more complicated stored procedure that goes on to do additional work and update the document. But I noticed the just reading the doccument in the stored procedure seems to cost almost double that of querying it directly. I'll try commenting out most of the validation and see if the RU cost goes down at all.
– Joshua Hayes
Dec 28 '18 at 14:12
With validation disabled there was no observable difference in RU cost of the stored proc. Interestingly though, the RU cost keeps changing from execution to execution on retrieval of different docs. What would cause this? I thought the RU charge should be the same on the same query executed over almost identical docs of the same size. Also, you mention that the SDK call for a query will cost less than the equivalent query in the stored proc? Why is that?
– Joshua Hayes
Dec 28 '18 at 14:21
I can't explain the variation you're seeing. But... point-reads don't follow the same path as the general query engine. There's documentation somewhere about it, but... general rule is something like 1RU for a 1K document via Read (something not achievable via query).
– David Makogon
Dec 28 '18 at 14:35
This isn't a query on alternate indexed paths though that I would expect would invoke a more expensive query op (4x more expensive according to the same M$ docs). In this case both the stored proc and portal / sdk query generate a query direct to the partition and document id lookup.The stored proc executes within the context of a single partition with a direct lookup to document id also. I appreciate your help and feedback on these cosmos questions as it's often difficult to get answers.
– Joshua Hayes
Dec 28 '18 at 14:54
add a comment |
0
0
0
Is it expected that a query executed via a stored proc has a higher RU charge (almost double), than that of the same query executed via the sdk or portal?
This test code shows both cases execute a query for a single document against partitionKey and document id.
Simple query executed via stored proc
Simple stored proc to test execution of a single document by id. As this is executed withing the scope of a single partition, it should result in a direct query against partition and doc id.
function testProc(id, props) {
var collection = getContext().getCollection();
var collectionLink = collection.getSelfLink();
var response = getContext().getResponse();
// Validate input.
if (!id) throw new Error("The id is undefined or null.");
if (!props) throw new Error("The update is undefined or null.");
tryQuery();
function tryQuery() {
var query = {query: "select * from root r where r.id = @id", parameters: [{name: "@id", value: id}]};
var isAccepted = collection.queryDocuments(collectionLink, query, function (err, documents, responseOptions) {
if (err) throw err;
if (documents.length == 1) {
// Update props
} else {
// Else a document with the given id does not exist..
throw new Error("Document not found.");
}
});
// If we hit execution bounds - throw an exception.
// This is highly unlikely given that this is a query by id; but is included to serve as an example for larger queries.
if (!isAccepted) {
throw new Error("The stored procedure timed out.");
}
}
}
Request Charge: 5.68 RU
Query direct in the portal
SELECT * FROM c where c.partitionKey = 'Tenant_8_u768f5c6b-ef6d-4cd9-8ad0-ae0c68eb9887@mail.com' and c.id = 'c12a3dbd-ad19-41d6-971b-79e0c7acec41'
Request Charge: 2.890 RUs
I can only assume according to the cosmos request units and throughput documentation that this discrepancy comes about due to the physical overhead of compute resources (cpu / memory) required in order to run the stored proc itself.
For a simple query to pull out a document by partition and id it seems rather high though.
azure azure-cosmosdb asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
Is it expected that a query executed via a stored proc has a higher RU charge (almost double), than that of the same query executed via the sdk or portal?
This test code shows both cases execute a query for a single document against partitionKey and document id.
Simple query executed via stored proc
Simple stored proc to test execution of a single document by id. As this is executed withing the scope of a single partition, it should result in a direct query against partition and doc id.
function testProc(id, props) {
var collection = getContext().getCollection();
var collectionLink = collection.getSelfLink();
var response = getContext().getResponse();
// Validate input.
if (!id) throw new Error("The id is undefined or null.");
if (!props) throw new Error("The update is undefined or null.");
tryQuery();
function tryQuery() {
var query = {query: "select * from root r where r.id = @id", parameters: [{name: "@id", value: id}]};
var isAccepted = collection.queryDocuments(collectionLink, query, function (err, documents, responseOptions) {
if (err) throw err;
if (documents.length == 1) {
// Update props
} else {
// Else a document with the given id does not exist..
throw new Error("Document not found.");
}
});
// If we hit execution bounds - throw an exception.
// This is highly unlikely given that this is a query by id; but is included to serve as an example for larger queries.
if (!isAccepted) {
throw new Error("The stored procedure timed out.");
}
}
}
Request Charge: 5.68 RU
Query direct in the portal
SELECT * FROM c where c.partitionKey = 'Tenant_8_u768f5c6b-ef6d-4cd9-8ad0-ae0c68eb9887@mail.com' and c.id = 'c12a3dbd-ad19-41d6-971b-79e0c7acec41'
Request Charge: 2.890 RUs
I can only assume according to the cosmos request units and throughput documentation that this discrepancy comes about due to the physical overhead of compute resources (cpu / memory) required in order to run the stored proc itself.
For a simple query to pull out a document by partition and id it seems rather high though.
azure azure-cosmosdb azure azure-cosmosdb asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
edited Dec 28 '18 at 15:00
Joshua Hayes
asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
asked Dec 28 '18 at 12:36
Joshua HayesJoshua Hayes
1,21811735
1,21811735
Have you tried running a version of your stored procedure with no validation / error checking, to see if that is adding to your RU cost? Also - fyi since you know the ID you're searching for, a direct read (via SDK call) will cost less than the equivalent query.
– David Makogon
Dec 28 '18 at 13:44
As indicated this was a test I put together when I noticed something was off with with a more complicated stored procedure that goes on to do additional work and update the document. But I noticed the just reading the doccument in the stored procedure seems to cost almost double that of querying it directly. I'll try commenting out most of the validation and see if the RU cost goes down at all.
– Joshua Hayes
Dec 28 '18 at 14:12
With validation disabled there was no observable difference in RU cost of the stored proc. Interestingly though, the RU cost keeps changing from execution to execution on retrieval of different docs. What would cause this? I thought the RU charge should be the same on the same query executed over almost identical docs of the same size. Also, you mention that the SDK call for a query will cost less than the equivalent query in the stored proc? Why is that?
– Joshua Hayes
Dec 28 '18 at 14:21
I can't explain the variation you're seeing. But... point-reads don't follow the same path as the general query engine. There's documentation somewhere about it, but... general rule is something like 1RU for a 1K document via Read (something not achievable via query).
– David Makogon
Dec 28 '18 at 14:35
This isn't a query on alternate indexed paths though that I would expect would invoke a more expensive query op (4x more expensive according to the same M$ docs). In this case both the stored proc and portal / sdk query generate a query direct to the partition and document id lookup.The stored proc executes within the context of a single partition with a direct lookup to document id also. I appreciate your help and feedback on these cosmos questions as it's often difficult to get answers.
– Joshua Hayes
Dec 28 '18 at 14:54
add a comment |
Have you tried running a version of your stored procedure with no validation / error checking, to see if that is adding to your RU cost? Also - fyi since you know the ID you're searching for, a direct read (via SDK call) will cost less than the equivalent query.
– David Makogon
Dec 28 '18 at 13:44
As indicated this was a test I put together when I noticed something was off with with a more complicated stored procedure that goes on to do additional work and update the document. But I noticed the just reading the doccument in the stored procedure seems to cost almost double that of querying it directly. I'll try commenting out most of the validation and see if the RU cost goes down at all.
– Joshua Hayes
Dec 28 '18 at 14:12
With validation disabled there was no observable difference in RU cost of the stored proc. Interestingly though, the RU cost keeps changing from execution to execution on retrieval of different docs. What would cause this? I thought the RU charge should be the same on the same query executed over almost identical docs of the same size. Also, you mention that the SDK call for a query will cost less than the equivalent query in the stored proc? Why is that?
– Joshua Hayes
Dec 28 '18 at 14:21
I can't explain the variation you're seeing. But... point-reads don't follow the same path as the general query engine. There's documentation somewhere about it, but... general rule is something like 1RU for a 1K document via Read (something not achievable via query).
– David Makogon
Dec 28 '18 at 14:35
This isn't a query on alternate indexed paths though that I would expect would invoke a more expensive query op (4x more expensive according to the same M$ docs). In this case both the stored proc and portal / sdk query generate a query direct to the partition and document id lookup.The stored proc executes within the context of a single partition with a direct lookup to document id also. I appreciate your help and feedback on these cosmos questions as it's often difficult to get answers.
– Joshua Hayes
Dec 28 '18 at 14:54
Have you tried running a version of your stored procedure with no validation / error checking, to see if that is adding to your RU cost? Also - fyi since you know the ID you're searching for, a direct read (via SDK call) will cost less than the equivalent query.
– David Makogon
Dec 28 '18 at 13:44
Have you tried running a version of your stored procedure with no validation / error checking, to see if that is adding to your RU cost? Also - fyi since you know the ID you're searching for, a direct read (via SDK call) will cost less than the equivalent query.
– David Makogon
Dec 28 '18 at 13:44
As indicated this was a test I put together when I noticed something was off with with a more complicated stored procedure that goes on to do additional work and update the document. But I noticed the just reading the doccument in the stored procedure seems to cost almost double that of querying it directly. I'll try commenting out most of the validation and see if the RU cost goes down at all.
– Joshua Hayes
Dec 28 '18 at 14:12
As indicated this was a test I put together when I noticed something was off with with a more complicated stored procedure that goes on to do additional work and update the document. But I noticed the just reading the doccument in the stored procedure seems to cost almost double that of querying it directly. I'll try commenting out most of the validation and see if the RU cost goes down at all.
– Joshua Hayes
Dec 28 '18 at 14:12
With validation disabled there was no observable difference in RU cost of the stored proc. Interestingly though, the RU cost keeps changing from execution to execution on retrieval of different docs. What would cause this? I thought the RU charge should be the same on the same query executed over almost identical docs of the same size. Also, you mention that the SDK call for a query will cost less than the equivalent query in the stored proc? Why is that?
– Joshua Hayes
Dec 28 '18 at 14:21
With validation disabled there was no observable difference in RU cost of the stored proc. Interestingly though, the RU cost keeps changing from execution to execution on retrieval of different docs. What would cause this? I thought the RU charge should be the same on the same query executed over almost identical docs of the same size. Also, you mention that the SDK call for a query will cost less than the equivalent query in the stored proc? Why is that?
– Joshua Hayes
Dec 28 '18 at 14:21
I can't explain the variation you're seeing. But... point-reads don't follow the same path as the general query engine. There's documentation somewhere about it, but... general rule is something like 1RU for a 1K document via Read (something not achievable via query).
– David Makogon
Dec 28 '18 at 14:35
I can't explain the variation you're seeing. But... point-reads don't follow the same path as the general query engine. There's documentation somewhere about it, but... general rule is something like 1RU for a 1K document via Read (something not achievable via query).
– David Makogon
Dec 28 '18 at 14:35
This isn't a query on alternate indexed paths though that I would expect would invoke a more expensive query op (4x more expensive according to the same M$ docs). In this case both the stored proc and portal / sdk query generate a query direct to the partition and document id lookup.The stored proc executes within the context of a single partition with a direct lookup to document id also. I appreciate your help and feedback on these cosmos questions as it's often difficult to get answers.
– Joshua Hayes
Dec 28 '18 at 14:54
This isn't a query on alternate indexed paths though that I would expect would invoke a more expensive query op (4x more expensive according to the same M$ docs). In this case both the stored proc and portal / sdk query generate a query direct to the partition and document id lookup.The stored proc executes within the context of a single partition with a direct lookup to document id also. I appreciate your help and feedback on these cosmos questions as it's often difficult to get answers.
– Joshua Hayes
Dec 28 '18 at 14:54
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53958741%2fcosmos-db-stored-procedure-ru-charge-is-higher-than-equivalent-query-via-sdk-or%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53958741%2fcosmos-db-stored-procedure-ru-charge-is-higher-than-equivalent-query-via-sdk-or%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you tried running a version of your stored procedure with no validation / error checking, to see if that is adding to your RU cost? Also - fyi since you know the ID you're searching for, a direct read (via SDK call) will cost less than the equivalent query.
– David Makogon
Dec 28 '18 at 13:44
As indicated this was a test I put together when I noticed something was off with with a more complicated stored procedure that goes on to do additional work and update the document. But I noticed the just reading the doccument in the stored procedure seems to cost almost double that of querying it directly. I'll try commenting out most of the validation and see if the RU cost goes down at all.
– Joshua Hayes
Dec 28 '18 at 14:12
With validation disabled there was no observable difference in RU cost of the stored proc. Interestingly though, the RU cost keeps changing from execution to execution on retrieval of different docs. What would cause this? I thought the RU charge should be the same on the same query executed over almost identical docs of the same size. Also, you mention that the SDK call for a query will cost less than the equivalent query in the stored proc? Why is that?
– Joshua Hayes
Dec 28 '18 at 14:21
I can't explain the variation you're seeing. But... point-reads don't follow the same path as the general query engine. There's documentation somewhere about it, but... general rule is something like 1RU for a 1K document via Read (something not achievable via query).
– David Makogon
Dec 28 '18 at 14:35
This isn't a query on alternate indexed paths though that I would expect would invoke a more expensive query op (4x more expensive according to the same M$ docs). In this case both the stored proc and portal / sdk query generate a query direct to the partition and document id lookup.The stored proc executes within the context of a single partition with a direct lookup to document id also. I appreciate your help and feedback on these cosmos questions as it's often difficult to get answers.
– Joshua Hayes
Dec 28 '18 at 14:54