locked
Cosmos Stored procedure performance RRS feed

  • Question

  • HI Team,

    I have a application in which i want to perform CRUD operation, i have two simple approach-

    1> Using stored procedure , i can perform CRUD operation.-https://docs.microsoft.com/en-us/azure/cosmos-db/programming

    2> Using cosmos queries to perform CRUD operation - https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-get-started

    Please let me know which approach is better or using stored procedure can cause any kind of performance issue ?


    Tuesday, May 1, 2018 6:43 AM

Answers

  • Hi Navdep,

    Cosmos DB stored procedures have one advantage over the SQL API and this is: 

    "A stored procedure is a piece of application logic written in JavaScript that is registered and executed against a collection as a single transaction. In Azure Cosmos DB, JavaScript is hosted in the same memory space as the database. Hence, requests made within stored procedures execute in the same scope of a database session. This enables Azure Cosmos DB to guarantee ACID (Atomicity, Consistency, Isolation, Durability) for all operations that are part of a single stored procedure."

    Stored Procedures

    You can perform the following tasks with store procedures:

    • Create a Stored Procedure
    • Replace a Stored Procedure
    • List Stored Procedures
    • Delete a Stored Procedure
    • Execute a Stored Procedure

    If your programming logic is static, and you look to perform the same basic operations, and given the durability, stored procedures are the way to go. You still are going to need to use REST to 

    The SQL API has more flexibility, including managing the stored procedures (via the tasks listed above). Depending what your trying to achieve, a hybrid approach could be useful. 

    I hope this helps.

    Regards,

    Mike




    Tuesday, May 1, 2018 6:13 PM