{"id":364416,"date":"2024-05-21T02:16:25","date_gmt":"2024-05-21T02:16:25","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=364416"},"modified":"-0001-11-30T00:00:00","modified_gmt":"2024-05-21T02:16:25","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=364416","title":{"rendered":"<span>Evaluating Performance: CosmosDB vs. Azure SQL<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<h3>Introduction<\/h3>\n<p>In the evolving landscape of database technology, choosing the right database management system is crucial for the efficiency and scalability of applications.  This article compares the performance between  Microsoft&#8217;s CosmosDB and MS SQL Server. We&#8217;ll examine how each database performs under various load conditions and share some interesting findings.<\/p>\n<h3>CosmosDB vs. Azure SQL Server: Understanding the Differences<\/h3>\n<h4>CosmosDB: A Multi-Model Database Service<\/h4>\n<p>CosmosDB stands out with its multi-model approach, supporting document, key-value, graph, and column-family data models. Its global distribution architecture and multi-master replication feature are designed to offer high availability and low latency for globally distributed applications.<\/p>\n<p>Key Features:<\/p>\n<ul>\n<li>\n<p><strong>Global Distribution:<\/strong> CosmosDB is inherently designed for global distribution, enabling data to be replicated in multiple regions worldwide.<\/p>\n<\/li>\n<li>\n<p><strong>Multi-Write Replicas:<\/strong> Offers support for multiple write and read replicas, enhancing data redundancy and availability.<\/p>\n<\/li>\n<li>\n<p><strong>Scalability:<\/strong> High scalability for handling large volumes of data with efficient resource management.<\/p>\n<\/li>\n<\/ul>\n<h4>Azure SQL Server: A Relational Database Veteran<\/h4>\n<p>Azure SQL Server, a cloud generation of the long-established relational DBMS, is known for its robust performance, security features, and complex query capabilities. It&#8217;s highly preferred for traditional enterprise applications requiring complex transactions and query processing.<\/p>\n<p>Key Features:<\/p>\n<ul>\n<li>\n<p><strong>Performance:<\/strong> Exceptional performance for complex queries and transaction processing.<\/p>\n<\/li>\n<li>\n<p><strong>Security:<\/strong> Advanced security features for data protection and access control.<\/p>\n<\/li>\n<li>\n<p><strong>Programmability<\/strong>:  Azure SQL offers extensive programmability, enabling users to write complex stored procedures, functions, and triggers, enhancing database functionality and automation.  <\/p>\n<\/li>\n<\/ul>\n<p>Azure SQL can be purchased in two pricing models: DTU(Database Transaction Units) and vCore. There is also a serverless offering, but all the serverless option does is turn off the database when it&#8217;s not used at all, and there are no active connections, which is very unlikely for the real production application.<\/p>\n<p>Cosmos DB for NoSql has only one purchase model: RU per second(request unit).  This approach aims for more consistent performance as each specific request, depending on the data amount, consistently uses a set number of  RUs. If a demand exceeds the database&#8217;s capacity, the database responds with a 429  HTTP status code, indicating the need to retry later. Cosmos DB&#8217;s SDK  automatically handles this error by retrying the request. Cosmos DB offers a serverless model where you&#8217;re billed only for the RUs you use. This can be pretty beneficial for applications with variable or unpredictable loads.<\/p>\n<h3>Performance Comparison: The Setup<\/h3>\n<p>Two instances were created with equivalent financial investment to compare the performance of CosmosDB and MS SQL Server objectively.<\/p>\n<p>Here are the parameters for the CosmosDB:<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f95\/cd5\/a2f\/f95cd5a2f0228d48de28b3209a86e679.png\" width=\"1586\" height=\"466\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/f95\/cd5\/a2f\/f95cd5a2f0228d48de28b3209a86e679.png\"\/><\/figure>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/a2a\/705\/cdc\/a2a705cdcb66547120b0d9131cabf4ac.png\" width=\"1596\" height=\"676\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/a2a\/705\/cdc\/a2a705cdcb66547120b0d9131cabf4ac.png\"\/><\/figure>\n<p>The monthly cost for this CosmosDB configuration is $446.20<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/13d\/b67\/178\/13db6717819359c4de80d3911774771a.png\" width=\"1592\" height=\"601\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/13d\/b67\/178\/13db6717819359c4de80d3911774771a.png\"\/><\/figure>\n<p>The monthly cost for this CosmosDB configuration is $456.25<\/p>\n<h2>Performance Comparison: The Approach<\/h2>\n<p>We will compare bulk insert operation, single insert operation and reading 20 rows by index. The tests for both databases will be conducted using a .NET application.  For Cosmos DB, its native SDK will be utilized, while for Azure SQL, the Entity Framework will be employed.  <\/p>\n<p>For test purposes, we will use an object with 25 properties of different types with a primary key and four non-unique indexes. The database size is 1 GB.<\/p>\n<p>For all the Cosmos DB inserts EnableContentResponseOnWrite = false is set to speed up the operations.<\/p>\n<h4>Bulk insert<\/h4>\n<p>10,000 records will be inserted. For Cosmos DB, AllowBulkExecution is enabled. This option combines multiple queries into a single request, increasing latency but improving the throughput. For the Azure SQL <a href=\"https:\/\/entityframework-extensions.net\/\" rel=\"noopener noreferrer nofollow\">Entity Framework Extensions<\/a> will be used to make a bulk insert.<\/p>\n<h4>Individual inserts<\/h4>\n<p>10,000 records will be inserted in 1 and 10 threads and 100,000 in 100 threads, emulating workload from different numbers of users simultaneously working with the application.<\/p>\n<h4>Select<\/h4>\n<p>10 rows will be selected by index each time. 10,000 select will be done in 1 and 10 threads.<\/p>\n<h3>Performance Comparison: The Results<\/h3>\n<p>Here are the total times and average time per operation in milliseconds. Both times reflect the <strong>wall time<\/strong>, not the thread time, so an increasing number of threads should reduce per operation time.<\/p>\n<div>\n<div class=\"table\">\n<table>\n<tbody>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"left\">Cosmos DB<\/p>\n<\/td>\n<td>\n<p align=\"left\">Azure SQL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Bulk Insert 10,000<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>16589 \/  1.6<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>3951 \/  0.4<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Insert 10,000 1 thread.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>57998 \/  5.7<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>28064 \/  2.8<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Insert 10,000 10 threads.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>54361 \/  5.4<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>7154 \/  0.7<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Insert 100,000 100 threads.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>129186 \/ 12.9<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>78304 \/  7.8<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Select 10,000 1 thread.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>91371 \/  9.1<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>34810 \/  3.4<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Select 10,000 10 threads.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>39492 \/  3.9<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>16279 \/  1.6<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>For Cosmos DB, we can estimate the minimum time needed for an operation. In our setup with 5500 RU\/s, a single insert operation consumes 7.05 RU, allowing for about 780 operations every second. This translates to a minimum possible time of around 12.8 seconds. For bulk operations, the actual time taken is about 20% longer than this minimum, but for individual operations, the delay is significantly greater, and RUs don&#8217;t restrict the throughput.<\/p>\n<p>Interestingly, increasing the number of threads didn&#8217;t enhance the performance of Cosmos DB. Even with more than half of the RU\/s remaining unused, this didn&#8217;t lead to any significant speed-up in the process.<\/p>\n<p>Compared to Azure SQL, Cosmos DB is significantly slower for all the operations.<\/p>\n<h3>Conclusion: Choosing the Right DBMS for Your Needs<\/h3>\n<p>While MS SQL Server shows superior performance for the same monetary investment, it&#8217;s crucial to consider the specific requirements of your application. CosmosDB, with its multi-region and multi-master features, is an ideal choice for applications requiring global distribution and high availability.<\/p>\n<p>Ultimately, the decision should be based on a thorough analysis of the application&#8217;s requirements, budget, and scalability needs. In scenarios where global distribution and multi-master capabilities are not a priority, MS SQL Server may offer a more cost-effective and performance-oriented solution.<\/p>\n<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/784178\/\"> https:\/\/habr.com\/ru\/articles\/784178\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<h3>Introduction<\/h3>\n<p>In the evolving landscape of database technology, choosing the right database management system is crucial for the efficiency and scalability of applications.  This article compares the performance between  Microsoft&#8217;s CosmosDB and MS SQL Server. We&#8217;ll examine how each database performs under various load conditions and share some interesting findings.<\/p>\n<h3>CosmosDB vs. Azure SQL Server: Understanding the Differences<\/h3>\n<h4>CosmosDB: A Multi-Model Database Service<\/h4>\n<p>CosmosDB stands out with its multi-model approach, supporting document, key-value, graph, and column-family data models. Its global distribution architecture and multi-master replication feature are designed to offer high availability and low latency for globally distributed applications.<\/p>\n<p>Key Features:<\/p>\n<ul>\n<li>\n<p><strong>Global Distribution:<\/strong> CosmosDB is inherently designed for global distribution, enabling data to be replicated in multiple regions worldwide.<\/p>\n<\/li>\n<li>\n<p><strong>Multi-Write Replicas:<\/strong> Offers support for multiple write and read replicas, enhancing data redundancy and availability.<\/p>\n<\/li>\n<li>\n<p><strong>Scalability:<\/strong> High scalability for handling large volumes of data with efficient resource management.<\/p>\n<\/li>\n<\/ul>\n<h4>Azure SQL Server: A Relational Database Veteran<\/h4>\n<p>Azure SQL Server, a cloud generation of the long-established relational DBMS, is known for its robust performance, security features, and complex query capabilities. It&#8217;s highly preferred for traditional enterprise applications requiring complex transactions and query processing.<\/p>\n<p>Key Features:<\/p>\n<ul>\n<li>\n<p><strong>Performance:<\/strong> Exceptional performance for complex queries and transaction processing.<\/p>\n<\/li>\n<li>\n<p><strong>Security:<\/strong> Advanced security features for data protection and access control.<\/p>\n<\/li>\n<li>\n<p><strong>Programmability<\/strong>:  Azure SQL offers extensive programmability, enabling users to write complex stored procedures, functions, and triggers, enhancing database functionality and automation.  <\/p>\n<\/li>\n<\/ul>\n<p>Azure SQL can be purchased in two pricing models: DTU(Database Transaction Units) and vCore. There is also a serverless offering, but all the serverless option does is turn off the database when it&#8217;s not used at all, and there are no active connections, which is very unlikely for the real production application.<\/p>\n<p>Cosmos DB for NoSql has only one purchase model: RU per second(request unit).  This approach aims for more consistent performance as each specific request, depending on the data amount, consistently uses a set number of  RUs. If a demand exceeds the database&#8217;s capacity, the database responds with a 429  HTTP status code, indicating the need to retry later. Cosmos DB&#8217;s SDK  automatically handles this error by retrying the request. Cosmos DB offers a serverless model where you&#8217;re billed only for the RUs you use. This can be pretty beneficial for applications with variable or unpredictable loads.<\/p>\n<h3>Performance Comparison: The Setup<\/h3>\n<p>Two instances were created with equivalent financial investment to compare the performance of CosmosDB and MS SQL Server objectively.<\/p>\n<p>Here are the parameters for the CosmosDB:<\/p>\n<figure class=\"full-width\"><\/figure>\n<figure class=\"full-width\"><\/figure>\n<p>The monthly cost for this CosmosDB configuration is $446.20<\/p>\n<figure class=\"full-width\"><\/figure>\n<p>The monthly cost for this CosmosDB configuration is $456.25<\/p>\n<h2>Performance Comparison: The Approach<\/h2>\n<p>We will compare bulk insert operation, single insert operation and reading 20 rows by index. The tests for both databases will be conducted using a .NET application.  For Cosmos DB, its native SDK will be utilized, while for Azure SQL, the Entity Framework will be employed.  <\/p>\n<p>For test purposes, we will use an object with 25 properties of different types with a primary key and four non-unique indexes. The database size is 1 GB.<\/p>\n<p>For all the Cosmos DB inserts EnableContentResponseOnWrite = false is set to speed up the operations.<\/p>\n<h4>Bulk insert<\/h4>\n<p>10,000 records will be inserted. For Cosmos DB, AllowBulkExecution is enabled. This option combines multiple queries into a single request, increasing latency but improving the throughput. For the Azure SQL <a href=\"https:\/\/entityframework-extensions.net\/\" rel=\"noopener noreferrer nofollow\">Entity Framework Extensions<\/a> will be used to make a bulk insert.<\/p>\n<h4>Individual inserts<\/h4>\n<p>10,000 records will be inserted in 1 and 10 threads and 100,000 in 100 threads, emulating workload from different numbers of users simultaneously working with the application.<\/p>\n<h4>Select<\/h4>\n<p>10 rows will be selected by index each time. 10,000 select will be done in 1 and 10 threads.<\/p>\n<h3>Performance Comparison: The Results<\/h3>\n<p>Here are the total times and average time per operation in milliseconds. Both times reflect the <strong>wall time<\/strong>, not the thread time, so an increasing number of threads should reduce per operation time.<\/p>\n<div>\n<div class=\"table\">\n<table>\n<tbody>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"left\">Cosmos DB<\/p>\n<\/td>\n<td>\n<p align=\"left\">Azure SQL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Bulk Insert 10,000<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>16589 \/  1.6<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>3951 \/  0.4<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Insert 10,000 1 thread.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>57998 \/  5.7<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>28064 \/  2.8<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Insert 10,000 10 threads.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>54361 \/  5.4<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>7154 \/  0.7<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Insert 100,000 100 threads.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>129186 \/ 12.9<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>78304 \/  7.8<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Select 10,000 1 thread.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>91371 \/  9.1<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>34810 \/  3.4<\/code><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td data-colwidth=\"215\" width=\"215\">\n<p align=\"left\">Select 10,000 10 threads.<\/p>\n<\/td>\n<td data-colwidth=\"243\" width=\"243\">\n<p align=\"right\"><code>39492 \/  3.9<\/code><\/p>\n<\/td>\n<td>\n<p align=\"right\"><code>16279 \/  1.6<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>For Cosmos DB, we can estimate the minimum time needed for an operation. In our setup with 5500 RU\/s, a single insert operation consumes 7.05 RU, allowing for about 780 operations every second. This translates to a minimum possible time of around 12.8 seconds. For bulk operations, the actual time taken is about 20% longer than this minimum, but for individual operations, the delay is significantly greater, and RUs don&#8217;t restrict the throughput.<\/p>\n<p>Interestingly, increasing the number of threads didn&#8217;t enhance the performance of Cosmos DB. Even with more than half of the RU\/s remaining unused, this didn&#8217;t lead to any significant speed-up in the process.<\/p>\n<p>Compared to Azure SQL, Cosmos DB is significantly slower for all the operations.<\/p>\n<h3>Conclusion: Choosing the Right DBMS for Your Needs<\/h3>\n<p>While MS SQL Server shows superior performance for the same monetary investment, it&#8217;s crucial to consider the specific requirements of your application. CosmosDB, with its multi-region and multi-master features, is an ideal choice for applications requiring global distribution and high availability.<\/p>\n<p>Ultimately, the decision should be based on a thorough analysis of the application&#8217;s requirements, budget, and scalability needs. In scenarios where global distribution and multi-master capabilities are not a priority, MS SQL Server may offer a more cost-effective and performance-oriented solution.<\/p>\n<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/784178\/\"> https:\/\/habr.com\/ru\/articles\/784178\/<\/a><br \/><\/br><\/br><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-364416","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/364416","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=364416"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/364416\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=364416"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=364416"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=364416"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}