We have developed several large websites using the Azure Platform. The only step for success in a cloud environment is to minimize round-trips over the networks - higher performance infrastructure cannot fix the root cause. The critical technology in a Web/SQL Azure application for Performance is multi-recordset calls to Stored Procedures in SQL Server so that we have one round-trip from our webserver to the SQL Server (each trip is 100ms minimum in Azure) per page request. That single stored procedure that is called returns every dataset needed for the page view in one call - we then typecast and read the result sets from the encoded result. This alone is the difference between a 15 second page view time and 250ms. When applying the same methodology to Redis and Blob/Filesystems you will see repeatable and highly scalable response times in the order of <500ms. We have "tweaked" NopCommerce this way to achieve performance on Azure with a single server to 1,000 requests per second with average response time of 600ms or consistently <300ms with 50 request per second (S2 App Service / DS11 SQL Web VM). This change as well as async calls to everything should be the highest priority development task to NopCommerce. Then it would be world-class and really showcase Microsoft Technologies.
Thanks for this. Is there any reason this maybe isnt nopcommerce primary focus? Do we know if it is?
Performance testing nopCommerce 3.70 on Azure using Redis + Blob + Azure SQL
- 10
- 210
- 8/8/2012
- Israel
partnumber wrote:
Can you please give an example?
We tried to go that way but is seems that all of those small SQL call cannot be gathered to one SQL because each one of them depends on the result of the previous one, so you cannot create SQL script until the previous SQL result returned to C# and processed for next SQL call.
If you managed to have a multi recordset even in part of the code it would be great for the performance of nop.
We have developed several large websites using the Azure Platform. The only step for success in a cloud environment is to minimize round-trips over the networks - higher performance infrastructure cannot fix the root cause. The critical technology in a Web/SQL Azure application for Performance is multi-recordset calls to Stored Procedures in SQL Server so that we have one round-trip from our webserver to the SQL Server (each trip is 100ms minimum in Azure) per page request. That single stored procedure that is called returns every dataset needed for the page view in one call - we then typecast and read the result sets from the encoded result. This alone is the difference between a 15 second page view time and 250ms. When applying the same methodology to Redis and Blob/Filesystems you will see repeatable and highly scalable response times in the order of <500ms. We have "tweaked" NopCommerce this way to achieve performance on Azure with a single server to 1,000 requests per second with average response time of 600ms or consistently <300ms with 50 request per second (S2 App Service / DS11 SQL Web VM). This change as well as async calls to everything should be the highest priority development task to NopCommerce. Then it would be world-class and really showcase Microsoft Technologies.
Can you please give an example?
We tried to go that way but is seems that all of those small SQL call cannot be gathered to one SQL because each one of them depends on the result of the previous one, so you cannot create SQL script until the previous SQL result returned to C# and processed for next SQL call.
If you managed to have a multi recordset even in part of the code it would be great for the performance of nop.
- 132
- 850
- 12/17/2013
- United States
Hello PartNumber,
For the benefit of the community and maybe to help steer the Nop team a little, could you share some details of what exactly you did?
partnumber wrote:
For the benefit of the community and maybe to help steer the Nop team a little, could you share some details of what exactly you did?
partnumber wrote:
We have developed several large websites using the Azure Platform. The only step for success in a cloud environment is to minimize round-trips over the networks - higher performance infrastructure cannot fix the root cause. The critical technology in a Web/SQL Azure application for Performance is multi-recordset calls to Stored Procedures in SQL Server so that we have one round-trip from our webserver to the SQL Server (each trip is 100ms minimum in Azure) per page request. That single stored procedure that is called returns every dataset needed for the page view in one call - we then typecast and read the result sets from the encoded result. This alone is the difference between a 15 second page view time and 250ms. When applying the same methodology to Redis and Blob/Filesystems you will see repeatable and highly scalable response times in the order of <500ms. We have "tweaked" NopCommerce this way to achieve performance on Azure with a single server to 1,000 requests per second with average response time of 600ms or consistently <300ms with 50 request per second (S2 App Service / DS11 SQL Web VM). This change as well as async calls to everything should be the highest priority development task to NopCommerce. Then it would be world-class and really showcase Microsoft Technologies.
- 33
- 355
- 3/29/2016
- Germany
Can you please share your source code changes for that with us, as here are many users of nopCommerce having the same issues with database performance?
partnumber wrote:
partnumber wrote:
We have developed several large websites using the Azure Platform. The only step for success in a cloud environment is to minimize round-trips over the networks - higher performance infrastructure cannot fix the root cause. The critical technology in a Web/SQL Azure application for Performance is multi-recordset calls to Stored Procedures in SQL Server so that we have one round-trip from our webserver to the SQL Server (each trip is 100ms minimum in Azure) per page request. That single stored procedure that is called returns every dataset needed for the page view in one call - we then typecast and read the result sets from the encoded result. This alone is the difference between a 15 second page view time and 250ms. When applying the same methodology to Redis and Blob/Filesystems you will see repeatable and highly scalable response times in the order of <500ms. We have "tweaked" NopCommerce this way to achieve performance on Azure with a single server to 1,000 requests per second with average response time of 600ms or consistently <300ms with 50 request per second (S2 App Service / DS11 SQL Web VM). This change as well as async calls to everything should be the highest priority development task to NopCommerce. Then it would be world-class and really showcase Microsoft Technologies.
- 79
- 1091
- 5/5/2010
- Spain
partnumber wrote:
I fully agree with this sentence. Every time I see the plan for the next nop release and I see it's a long list of little new features but fundamental "fixes" like this are not included I lose faith in this framework. In this has happened for many many releases already... I have very little faith left.
This change as well as async calls to everything should be the highest priority development task to NopCommerce. Then it would be world-class and really showcase Microsoft Technologies.
I fully agree with this sentence. Every time I see the plan for the next nop release and I see it's a long list of little new features but fundamental "fixes" like this are not included I lose faith in this framework. In this has happened for many many releases already... I have very little faith left.
- 132
- 850
- 12/17/2013
- United States
I second that. Performance and scalability are key and in today's world where customers are finicky, e-tailers need to compete with Amazon, and apps are moving to the cloud. As such, applications need to refactor to make the cloud hosting option viable, or shall otherwise become extinct.
We run our own site as well as some of our customers on Nop and have moved ours to Azure for testing, but cannot recommend it for our customers. Where service providers like us implement Nop for customers, we want to be able to offer a scalable and performant solution at a reasonable cost. Cloud platforms like Azure and AWS allow us to do that in theory, but only if the software works well in that environment.
If this issue is not going to be addressed soon, we will be forced to change directions. Now that nop is using Git instead of CodePlex, how do users like us vote on development items to help set priority for the Nop team? In searching for "performance" there are a few Issues on Git, but none that I see related to reducing database chattiness.
fcastells wrote:
We run our own site as well as some of our customers on Nop and have moved ours to Azure for testing, but cannot recommend it for our customers. Where service providers like us implement Nop for customers, we want to be able to offer a scalable and performant solution at a reasonable cost. Cloud platforms like Azure and AWS allow us to do that in theory, but only if the software works well in that environment.
If this issue is not going to be addressed soon, we will be forced to change directions. Now that nop is using Git instead of CodePlex, how do users like us vote on development items to help set priority for the Nop team? In searching for "performance" there are a few Issues on Git, but none that I see related to reducing database chattiness.
fcastells wrote:
This change as well as async calls to everything should be the highest priority development task to NopCommerce. Then it would be world-class and really showcase Microsoft Technologies.
I fully agree with this sentence. Every time I see the plan for the next nop release and I see it's a long list of little new features but fundamental "fixes" like this are not included I lose faith in this framework. In this has happened for many many releases already... I have very little faith left.
I fully agree with this sentence. Every time I see the plan for the next nop release and I see it's a long list of little new features but fundamental "fixes" like this are not included I lose faith in this framework. In this has happened for many many releases already... I have very little faith left.
- 1
- 5
- 3/30/2017
- Switzerland
ArielDeil wrote:
Hi Ariel!
Just in case non-nop specific code can help you: from my experience there are two approaches - stored procedures with multiple result sets and eager loading in entity framework.
Stored procedures with multiple recordsets are very good for preloading limited number of really big sets of data. You can still call them through EF and project results to context DbSets (you need to map result to desired entity from model), here in more detail
Eager Loading is very nice approach for optimizing existing code without adding stored procedures and w/o changes in model. You just disable lazy loading on DB chatty page and define what should be preloaded. Just continue add Inlcude() calls as described here (Eager Loading Multiple Levels) until you stop gettings NullRef exceptions:
We have developed several large websites using the Azure Platform. The only step for success in a cloud environment is to minimize round-trips over the networks - higher performance infrastructure cannot fix the root cause. The critical technology in a Web/SQL Azure application for Performance is multi-recordset calls to Stored Procedures in SQL Server so that we have one round-trip from our webserver to the SQL Server (each trip is 100ms minimum in Azure) per page request. That single stored procedure that is called returns every dataset needed for the page view in one call - we then typecast and read the result sets from the encoded result. This alone is the difference between a 15 second page view time and 250ms. When applying the same methodology to Redis and Blob/Filesystems you will see repeatable and highly scalable response times in the order of <500ms. We have "tweaked" NopCommerce this way to achieve performance on Azure with a single server to 1,000 requests per second with average response time of 600ms or consistently <300ms with 50 request per second (S2 App Service / DS11 SQL Web VM). This change as well as async calls to everything should be the highest priority development task to NopCommerce. Then it would be world-class and really showcase Microsoft Technologies.
Can you please give an example?
We tried to go that way but is seems that all of those small SQL call cannot be gathered to one SQL because each one of them depends on the result of the previous one, so you cannot create SQL script until the previous SQL result returned to C# and processed for next SQL call.
If you managed to have a multi recordset even in part of the code it would be great for the performance of nop.
Can you please give an example?
We tried to go that way but is seems that all of those small SQL call cannot be gathered to one SQL because each one of them depends on the result of the previous one, so you cannot create SQL script until the previous SQL result returned to C# and processed for next SQL call.
If you managed to have a multi recordset even in part of the code it would be great for the performance of nop.
Hi Ariel!
Just in case non-nop specific code can help you: from my experience there are two approaches - stored procedures with multiple result sets and eager loading in entity framework.
Stored procedures with multiple recordsets are very good for preloading limited number of really big sets of data. You can still call them through EF and project results to context DbSets (you need to map result to desired entity from model), here in more detail
Eager Loading is very nice approach for optimizing existing code without adding stored procedures and w/o changes in model. You just disable lazy loading on DB chatty page and define what should be preloaded. Just continue add Inlcude() calls as described here (Eager Loading Multiple Levels) until you stop gettings NullRef exceptions:
- 36
- 426
- 6/12/2014
- United Kingdom
The category listings and home page would be the main ones to optimise. I'm not sure if eager loading will work considering the repository per entity design pattern used. I think custom LINQ queries or stored procedures that project the data directly into a viewmodel is the way to go. Entity framework is great for line of business CRUD applications (admin), the front end of an e-commerce site is not one of these situations.
- 33
- 355
- 3/29/2016
- Germany
pdunleavy75 wrote:
This is what I am telling my supervisor the whole day ;-)
Stored Procedures are nothing bad. And outsourcing query logic to the database server itself is also nothing bad. If you have the knowlege of writing fast, high performing TSQL queries, use it!
The problem of nopCommerce is that in development everything is working fine and with only a handful of categories and some hundreds of products it works well in production. In our case we have 600 categories and 150.000 products and it still is performing good in development or with even a handful of users on the production site, but if there are 300, 500 or even more users with active session (what is normal business for us) it does not perform well in Azure enviroment and even the application will fail regularly. The database is under high pressure without using redis cache, but using redis cache is slowing down the whole application even for a handful of users in azure.
We are still investigating why redis is not working for our use case within azure enviroment. Could be the per request memory cache that is used to cache every redis request locally for the current request. The local size of memory seems not to be enough (even with 7 gigs of RAM). The redis caching should be revised too cause there should be no need to cache all redis requests in local memory per request or only for those requests that will occure more than once (and I think that could be prevented too). Or there should be a global local memory cache for non personalized requests to redis (but then I do not really know why to use redis at all). That would help to lower (local) memory pressure a lot.
The category listings and home page would be the main ones to optimise. I'm not sure if eager loading will work considering the repository per entity design pattern used. I think custom LINQ queries or stored procedures that project the data directly into a viewmodel is the way to go. Entity framework is great for line of business CRUD applications (admin), the front end of an e-commerce site is not one of these situations.
This is what I am telling my supervisor the whole day ;-)
Stored Procedures are nothing bad. And outsourcing query logic to the database server itself is also nothing bad. If you have the knowlege of writing fast, high performing TSQL queries, use it!
The problem of nopCommerce is that in development everything is working fine and with only a handful of categories and some hundreds of products it works well in production. In our case we have 600 categories and 150.000 products and it still is performing good in development or with even a handful of users on the production site, but if there are 300, 500 or even more users with active session (what is normal business for us) it does not perform well in Azure enviroment and even the application will fail regularly. The database is under high pressure without using redis cache, but using redis cache is slowing down the whole application even for a handful of users in azure.
We are still investigating why redis is not working for our use case within azure enviroment. Could be the per request memory cache that is used to cache every redis request locally for the current request. The local size of memory seems not to be enough (even with 7 gigs of RAM). The redis caching should be revised too cause there should be no need to cache all redis requests in local memory per request or only for those requests that will occure more than once (and I think that could be prevented too). Or there should be a global local memory cache for non personalized requests to redis (but then I do not really know why to use redis at all). That would help to lower (local) memory pressure a lot.