{"id":11147,"date":"2021-05-27T08:01:06","date_gmt":"2021-05-27T15:01:06","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=11147"},"modified":"2025-06-13T19:40:16","modified_gmt":"2025-06-14T02:40:16","slug":"sql-nosql-comparison-aspnet-application","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/sql-nosql-comparison-aspnet-application\/","title":{"rendered":"SQL and NoSQL comparison: ASP.NET application"},"content":{"rendered":"
\n

This SQL and NoSQL comparison is the next step after converting your SQL Server database to Couchbase. In the previous post<\/a>, I copied AdventureWorks from SQL Server to Couchbase.<\/p>\n<\/div>\n

\n

In this post, I\u2019m going to show an ASP.NET Core application that uses SQL Server, and how that same application would use Couchbase. If you\u2019d like to follow along, you can check out the SqlServerToCouchbase project<\/a> on GitHub.<\/p>\n<\/div>\n

\n

Unlike the previous post, I\u2019m making no attempt at an “automatic” conversion of an application. Instead, think of this more as a SQL and NoSQL comparison at the application level.<\/p>\n<\/div>\n

\n

ASP.NET SQL Server Applications<\/h2>\n
\n
\n

I created a very simple ASP.NET Core REST API style application. I used Entity Framework, but if you\u2019re using Dapper, ADO.NET, NHibernate, etc, you should still be able to follow along.<\/p>\n<\/div>\n

\n

Each endpoint returns JSON. I\u2019ve also added Swashbuckle to the project, so you can issue requests right from your browser via OpenAPI.<\/p>\n<\/div>\n<\/div>\n<\/div>\n

\n

ASP.NET Couchbase Server Application<\/h2>\n
\n
\n

The Couchbase version of the application returns the same data, because it\u2019s using the same SQL Server AdventureWorks data.<\/p>\n<\/div>\n

\n

In the application, I\u2019m using the Couchbase .NET SDK<\/a> and Couchbase Transactions<\/a> libraries. (You could use Linq2Couchbase<\/a> as a type of Entity Framework replacement).<\/p>\n<\/div>\n

\n

Otherwise, the application is the same, providing a SQL and NoSQL comparison (and contrast). The endpoints are returning JSON, and Swashbuckle is installed.<\/p>\n<\/div>\n

\n

There\u2019s one controller in each sample. Let\u2019s go through each endpoint in the controller and perform a SQL and NoSQL comparison.<\/p>\n<\/div>\n<\/div>\n<\/div>\n

\n

SQL and NoSQL Comparison: Get by ID<\/h2>\n
\n
\n

Let\u2019s start with the GetPersonByIdAsync<\/code> endpoint. Given a person ID, this endpoint returns the Person data for the given ID.<\/p>\n<\/div>\n

\n

SQL Server<\/h3>\n
\n

Here\u2019s the SQL Server example using Entity Framework:<\/p>\n<\/div>\n

\n
\n
[HttpGet(\"\/person\/{personId}\")]\r\npublic async Task<IActionResult> GetPersonByIdAsync(int personId)\r\n{\r\n    var person = await _context.Persons\r\n        .SingleOrDefaultAsync(p => p.BusinessEntityID == personId);\r\n\r\n    return Ok(person);\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
I also wrote another version of this method, called GetPersonByIdRawAsync<\/code> which uses a “raw” SQL query. This query is very similar to the one that Entity Framework (above) ultimately generates, and it\u2019s similar to a Dapper approach.<\/p>\n<\/div>\n
\n
\n
[HttpGet(\"\/personRaw\/{personId}\")]\r\npublic async Task<IActionResult> GetPersonByIdRawAsync(int personId)\r\n{\r\n    var person = await _context.Persons\r\n        .FromSqlRaw(@\"SELECT * FROM Person.Person WHERE BusinessEntityID = {0}\", personId)\r\n        .SingleOrDefaultAsync();\r\n\r\n    return Ok(person);\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
Note that either way, a SQL query is being executed.<\/p>\n<\/div>\n
\n
With N1QL, we could query the data in Couchbase in a very similar way. Here\u2019s the GetPersonByIdRawAsync<\/code> in the Couchbase project:<\/p>\n<\/div>\n
\n
\n
[HttpGet(\"\/personRaw\/{personId}\")]\r\npublic async Task<IActionResult> GetPersonByIdRawAsync(int personId)\r\n{\r\n    var bucket = await _bucketProvider.GetBucketAsync();\r\n    var cluster = bucket.Cluster;\r\n    var personResult = await cluster.QueryAsync<Person>(@\"\r\n        SELECT p.* FROM AdventureWorks2016.Person.Person p WHERE p.BusinessEntityID = $personId\",\r\n        new QueryOptions().Parameter(\"personId\", personId));\r\n    return Ok(await personResult.Rows.SingleOrDefaultAsync());\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
(There\u2019s an extra step going from “bucket” to “cluster”. This could be skipped, but I use bucket elsewhere in the controller, so I left it in).<\/p>\n<\/div>\n
\n
However, using a N1QL query involves some extra overhead (indexing, query parsing, etc). With Couchbase, if we already know the Person ID, we can skip a N1QL query and do a direct key\/value (K\/V) lookup.<\/p>\n<\/div>\n<\/div>\n
\n
Get by ID with K\/V<\/h3>\n
\n
The key is already known; it\u2019s given as an argument. Instead of using SQL, let\u2019s do a key\/value lookup. I did this in an endpoint method called GetPersonByIdAsync<\/code>:<\/p>\n<\/div>\n
\n
\n
[HttpGet(\"\/person\/{personId}\")]\r\npublic async Task<IActionResult> GetPersonByIdAsync(int personId)\r\n{\r\n    var bucket = await _bucketProvider.GetBucketAsync();\r\n    var scope = await bucket.ScopeAsync(\"Person\");\r\n    var coll = await scope.CollectionAsync(\"Person\");\r\n    var personDoc = await coll.GetAsync(personId.ToString());\r\n    return Ok(personDoc.ContentAs<Person>());\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
Unlike SQL Server, Couchbase supports a variety of APIs to interact with data. In this case, the key\/value lookup will be pulling the Person document directly from memory. There\u2019s no need to parse a SQL query or use any indexing. Key\/value lookups in Couchbase will often be measured in microseconds.<\/p>\n<\/div>\n
\n
My advice: use the key\/value lookup whenever you can.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
Get an expanded entity by ID<\/h2>\n
\n
\n
Data can be complex and span multiple tables (or multiple documents in the case of Couchbase). Depending on which tools you are using, you may have some functionality that can load related entities.<\/p>\n<\/div>\n
\n
For instance, with Entity Framework, you can use an Include<\/code> to pull in related entities, as shown in this GetPersonByIdExpandedAsync<\/code> example:<\/p>\n<\/div>\n
\n
\n
[HttpGet(\"\/personExpanded\/{personId}\")]\r\npublic async Task<IActionResult> GetPersonByIdExpandedAsync(int personId)\r\n{\r\n    var person = await _context.Persons\r\n        .Include(p => p.EmailAddresses)\r\n        .SingleOrDefaultAsync(p => p.BusinessEntityID == personId);\r\n\r\n    return Ok(person);\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
Behind the scenes, Entity Framework may generate a JOIN and\/or multiple SELECT queries to make this happen.<\/p>\n<\/div>\n
\n
This is where any O\/RM (not just Entity Framework) can be dangerous. Make sure to use a tool like SQL Profiler to see what queries are actually being run.<\/p>\n<\/div>\n
\n\n\n\n
\n
Note<\/div>\n<\/td>\n
O\/RMs can help, but in a SQL to NoSQL<\/a> comparison, it\u2019s important to remember that impedance mismatch is much less of a problem in the NoSQL world.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n
For the Couchbase sample, I\u2019m not using Entity Framework, but instead I can use the NEST syntax<\/a> that\u2019s part of the N1QL extensions on the SQL standard. Here\u2019s how the Couchbase version of GetPersonByIdExpandedAsync<\/code> looks:<\/p>\n<\/div>\n
\n
\n
[HttpGet(\"\/personExpanded\/{personId}\")]\r\npublic async Task<IActionResult> GetPersonByIdExpandedAsync(int personId)\r\n{\r\n    var bucket = await _bucketProvider.GetBucketAsync();\r\n    var cluster = bucket.Cluster;\r\n    var personResult = await cluster.QueryAsync<Person>(@\"\r\n        SELECT p.*, EmailAddresses\r\n        FROM AdventureWorks2016.Person.Person p\r\n        NEST AdventureWorks2016.Person.EmailAddress EmailAddresses ON EmailAddresses.BusinessEntityID = p.BusinessEntityID\r\n        WHERE p.BusinessEntityID = $personId\",\r\n        new QueryOptions().Parameter(\"personId\", personId));\r\n    return Ok(await personResult.Rows.SingleOrDefaultAsync());\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
NEST is a kind of JOIN that puts the JOINed data into a nested JSON object. Instead of using an O\/RM to map the data, this data can be directly serialized into C# objects.<\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n
Paging query<\/h2>\n
\n
\n
Let\u2019s look at an example where we do NOT have a single key to look up a piece of data. Let\u2019s look at a method that returns a “page” of results (perhaps to populate a UI grid or list).<\/p>\n<\/div>\n
\n
Paging in SQL Server<\/h3>\n
\n
Here\u2019s the SQL Server version of GetPersonsPageAsync<\/code>:<\/p>\n<\/div>\n
\n
\n
[HttpGet(\"\/persons\/page\/{pageNum}\")]\r\npublic async Task<IActionResult> GetPersonsPageAsync(int pageNum)\r\n{\r\n    var pageSize = 10;\r\n    var personPage = await _context.Persons\r\n        .OrderBy(p => p.LastName)\r\n        .Skip(pageNum * pageSize)\r\n        .Take(pageSize)\r\n        .Select(p => new { p.BusinessEntityID, p.FirstName, p.LastName })\r\n        .ToListAsync();\r\n    return Ok(personPage);\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
With Entity Framework, OrderBy<\/code>, Skip<\/code>, and Take<\/code> are typically used for paging. If we open up SQL Server Profiler, the SQL this generates looks something like this:<\/p>\n<\/div>\n
\n
\n
exec sp_executesql N'SELECT [p].[BusinessEntityID], [p].[FirstName], [p].[LastName]\r\nFROM [Person].[Person] AS [p]\r\nORDER BY [p].[LastName]\r\nOFFSET @__p_0 ROWS FETCH NEXT @__p_0 ROWS ONLY',N'@__p_0 int',@__p_0=10<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
OFFSET \u2026\u200b ROWS FETCH NEXT \u2026\u200b<\/code> is the syntax being used for paging here.<\/p>\n<\/div>\n<\/div>\n
\n
Paging in Couchbase<\/h3>\n
\n
Paging syntax always varies between SQL implementations. Couchbase leans more towards Oracle\/MySQL syntax in this regard. Here\u2019s the Couchbase version of GetPersonsPageAsync<\/code>:<\/p>\n<\/div>\n
\n
\n
[HttpGet(\"\/persons\/page\/{pageNum}\")]\r\npublic async Task<IActionResult> GetPersonsPageAsync(int pageNum)\r\n{\r\n    var pageSize = 10;\r\n    var bucket = await _bucketProvider.GetBucketAsync();\r\n    var bucketName = bucket.Name;\r\n    var cluster = bucket.Cluster;\r\n\r\n    var personPage = await cluster.QueryAsync<Person>($@\"\r\n        SELECT p.LastName, p.BusinessEntityID, p.FirstName\r\n        FROM `{bucketName}`.Person.Person p\r\n        WHERE p.LastName IS NOT MISSING\r\n        ORDER BY p.LastName\r\n        LIMIT {pageSize} OFFSET {(pageNum * pageSize)}\r\n    \");\r\n\r\n    return Ok(await personPage.Rows.ToListAsync());\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
In this case, LIMIT \u2026\u200b OFFSET \u2026\u200b<\/code> is being used.<\/p>\n<\/div>\n
\n
I also want to point out the WHERE p.LastName IS NOT MISSING<\/code>. Because Couchbase is a NoSQL database, the query engine cannot assume that LastName<\/code> will be in every document, even with ORDER BY p.LastName<\/code>. By adding this WHERE<\/code> clause, the query now knows which index to use. Without this, the query will take much longer to run.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
Update with an ACID transaction<\/h2>\n
\n
\n
With the relational-style model that we\u2019re using in both SQL Server and Couchbase for this example, ACID transactions will be important for both applications.<\/p>\n<\/div>\n
\n
In these examples, there is a PersonUpdateApi<\/code> which will allow the user to update both<\/strong> a person\u2019s name and their email address. Since this data is in two separate tables\/rows (SQL Server) or two separate documents (Couchbase), we want this to be an all-or-nothing, atomic operation.<\/p>\n<\/div>\n
\n\n\n\n
\n
Note<\/div>\n<\/td>\n
An ID is specified for both (to simplify the API), since it\u2019s possible (but rare in this dataset) for a person to have multiple email addresses.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n
ACID with Entity Framework<\/h3>\n
\n
Here\u2019s an example of an ACID transaction using Entity Framework to update both a row of data in the Person table and a row of data in the EmailAddress table.<\/p>\n<\/div>\n
\n
\n
[HttpPut(\"\/person\")]\r\npublic async Task<IActionResult> UpdatePurchaseOrderAsync(PersonUpdateApi personUpdateApi)\r\n{\r\n    var transaction = await _context.Database.BeginTransactionAsync();\r\n\r\n    try\r\n    {\r\n        \/\/ find the person\r\n        var person = await _context.Persons\r\n            .Include(p => p.EmailAddresses)\r\n            .SingleOrDefaultAsync(p => p.BusinessEntityID == personUpdateApi.PersonId);\r\n\r\n        \/\/ update name\r\n        person.FirstName = personUpdateApi.FirstName;\r\n        person.LastName = personUpdateApi.LastName;\r\n\r\n        \/\/ get the particular email address and update it\r\n        \/\/ if the supplied ID is invalid, this will throw an exception\r\n        var email = person.EmailAddresses.Single(e =>\r\n            e.EmailAddressID == personUpdateApi.EmailAddressId);\r\n        email.EmailAddress = personUpdateApi.EmailAddress;\r\n\r\n        await _context.SaveChangesAsync();\r\n\r\n        \/\/ commit transaction\r\n        await transaction.CommitAsync();\r\n\r\n        return Ok($\"Person {personUpdateApi.PersonId} name and email updated.\");\r\n    }\r\n    catch (Exception ex)\r\n    {\r\n        await transaction.RollbackAsync();\r\n        return BadRequest(\"Something went wrong, transaction rolled back\");\r\n    }\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
Note the four main parts of a transaction:<\/p>\n<\/div>\n
\n
    \n
  1. Begin transaction (_context.Database.BeginTransactionAsync();<\/code>)<\/li>\n
  2. try<\/code>\/catch<\/code><\/li>\n
  3. Commit transaction (await transaction.CommitAsync();<\/code>)<\/li>\n
  4. Rollback transaction in the catch<\/code> (transaction.RollbackAsync();<\/code>)<\/li>\n<\/ol>\n<\/div>\n
    \n
    This is an important feature where a SQL and NoSQL comparison has changed in recent years. With Couchbase, ACID transactions are now possible.<\/p>\n<\/div>\n<\/div>\n
    \n
    ACID with a Couchbase transaction<\/h3>\n
    \n
    With Couchbase, the API is slightly different, but the same steps are all there:<\/p>\n<\/div>\n
    \n
    \n
    [HttpPut(\"\/person\")]\r\npublic async Task<IActionResult> UpdatePurchaseOrderAsync(PersonUpdateApi personUpdateApi)\r\n{\r\n    \/\/ setup bucket, cluster, and collections\r\n    var bucket = await _bucketProvider.GetBucketAsync();\r\n    var scope = await bucket.ScopeAsync(\"Person\");\r\n    var personColl = await scope.CollectionAsync(\"Person\");\r\n    var emailColl = await scope.CollectionAsync(\"EmailAddress\");\r\n\r\n    \/\/ create transaction\r\n    var cluster = bucket.Cluster;\r\n    var transaction = Transactions.Create(cluster,\r\n        TransactionConfigBuilder.Create()\r\n            .DurabilityLevel(DurabilityLevel.None)\r\n            .Build());\r\n\r\n    try\r\n    {\r\n        await transaction.RunAsync(async (context) =>\r\n        {\r\n            \/\/ update person and email documents\r\n            \/\/ based on values passed in API object\r\n            var personKey = personUpdateApi.PersonId.ToString();\r\n            var emailKey = personKey + \"::\" + personUpdateApi.EmailAddressId.ToString();\r\n            var person = await context.GetAsync(personColl, personKey);\r\n            var email = await context.GetAsync(emailColl, emailKey);\r\n\r\n            var personDoc = person.ContentAs<dynamic>();\r\n            var emailDoc = email.ContentAs<dynamic>();\r\n\r\n            personDoc.FirstName = personUpdateApi.FirstName;\r\n            personDoc.LastName = personUpdateApi.LastName;\r\n            emailDoc.EmailAddress = personUpdateApi.EmailAddress;\r\n\r\n            await context.ReplaceAsync(person, personDoc);\r\n            await context.ReplaceAsync(email, emailDoc);\r\n        });\r\n        return Ok($\"Person {personUpdateApi.PersonId} name and email updated.\");\r\n    }\r\n    catch (Exception ex)\r\n    {\r\n        return BadRequest(\"Something went wrong, transaction rolled back.\");\r\n    }\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
    \n
    The same main steps are:<\/p>\n<\/div>\n
    \n
      \n
    1. Begin transaction (transaction.RunAsync( \u2026\u200b )<\/code>)<\/li>\n
    2. try<\/code>\/catch<\/code><\/li>\n
    3. Commit transaction (implied, but context.CommitAsync()<\/code> could be used)<\/li>\n
    4. Rollback transaction (again, implied, but context.RollbackAsync()<\/code> could be used).<\/li>\n<\/ol>\n<\/div>\n
      \n
      In both cases, we have an ACID transaction. Unlike<\/strong> SQL Server, however, we can later<\/strong> optimize and consolidate the data in Couchbase to reduce the amount of ACID transactions that we need and increase performance.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
      \n
      Stored Procedures: a SQL and NoSQL comparison<\/h2>\n
      \n
      \n
      Stored procedures are a sometimes controversial topic. Generally speaking, they can contain a lot of functionality and logic.<\/p>\n<\/div>\n
      \n
      Stored Procedure in SQL Server<\/h3>\n
      \n
      I\u2019ve created a stored procedure called “ListSubcomponents” (you can view the full details on GitHub<\/a>). With Entity Framework, you can use FromSqlRaw<\/code> to execute it and map the results to C# objects. I\u2019ve created a pseudo-entity C# object called ListSubcomponents<\/code> that\u2019s used just for this sproc:<\/p>\n<\/div>\n
      \n
      \n
      \/\/ sproc example - see ExampleStoredProcedure.sql\r\n[HttpGet(\"\/getListSubcomponents\/{listPriceMin}\/{listPriceMax}\")]\r\npublic async Task<IActionResult> GetListSubcomponents(decimal listPriceMin, decimal listPriceMax)\r\n{\r\n    var listPriceMinParam = new SqlParameter(\"@ListPriceMin\", SqlDbType.Decimal) {Value = listPriceMin };\r\n    var listPriceMaxParam = new SqlParameter(\"@ListPriceMax\", SqlDbType.Decimal) {Value = listPriceMax };\r\n\r\n    var result = await _context.ListSubcomponents\r\n        .FromSqlRaw(\"EXECUTE dbo.ListSubcomponents @ListPriceMin, @ListPriceMax\", listPriceMinParam, listPriceMaxParam)\r\n        .ToListAsync();\r\n\r\n    return Ok(result);\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
      \n
      The stored procedure has two parameters.<\/p>\n<\/div>\n<\/div>\n
      \n
      Couchbase User-defined function<\/h3>\n
      \n
      Couchbase doesn\u2019t have anything called a “stored procedure” (yet), but it does have something called a user-defined function (UDF) that can also contain complex logic when necessary.<\/p>\n<\/div>\n
      \n
      I created a UDF called ListSubcomponents<\/code> (which you can also view on GitHub<\/a>) which matches the functionality of the SQL Server sproc.<\/p>\n<\/div>\n
      \n
      Here\u2019s how to execute that UDF from ASP.NET:<\/p>\n<\/div>\n
      \n
      \n
      \/\/ sproc example - see ExampleStoredProcedure.sql\r\n[HttpGet(\"\/getListSubcomponents\/{listPriceMin}\/{listPriceMax}\")]\r\npublic async Task<IActionResult> GetListSubcomponents(decimal listPriceMin, decimal listPriceMax)\r\n{\r\n    var bucket = await _bucketProvider.GetBucketAsync();\r\n    var cluster = bucket.Cluster;\r\n\r\n    var options = new QueryOptions();\r\n    options.Parameter(\"$listPriceMin\", listPriceMin);\r\n    options.Parameter(\"$listPriceMax\", listPriceMax);\r\n\r\n    var result = await cluster.QueryAsync<ListSubcomponent>(\r\n        \"SELECT l.* FROM ListSubcomponents($listPriceMin, $listPriceMax) l\", options);\r\n\r\n    return Ok(await result.Rows.ToListAsync());\r\n}<\/code><\/pre>\n<\/div>\n<\/div>\n
      \n
      Invoking it in Couchbase with two parameters is very similar to using FromSqlRaw with Entity Framework.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
      \n
      Performance – SQL to NoSQL Comparison<\/h2>\n
      \n
      \n
      Now that I\u2019ve converted the app to use Couchbase, does the new version run at least as fast as the old SQL Server version?<\/p>\n<\/div>\n
      \n
      It\u2019s a complicated question to answer because:<\/p>\n<\/div>\n
      \n