Hi,
I would like to speak again about an old and big problem since nop v2 : NOPCOMMERCE IS TOO SLOW!
I know that when you take a fresh installation with sample data response time are correct, but this is not reality. I have more products, with more variants, with many customers, orders and discounts.
.NET is a wonderful platform with high performance, running on powerful servers, ASP MVC is lightweight compared to ASP, SQL server is an Amazing tool, and EF is...
But I'm currently having very bad performance on nopCommerce. Each version is announcing performance improvements but I can't see them. Today I migrated one of my web sites to nop 3.3, I hoped an amelioration but it takes from 0.8 to 5 seconds to load a page :(
I'm running on the server itself, so it's really the execution time.
My idea is that perf optimization should be the main goal of the next release. There are many ways to optimize the software, but what I can see in each version is just caching. This is one direction but all others are ignored.
For example:
- The worst process in all nop development is ProductLoadAllPaged store procedure. In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. Can you imagine that in a category page containing 20 grouped products, ProductLoadAllPaged is called minimum 21 times, each time a table is created in tempdb, and dropped...
Proposition: Create specific procedures for specific situations. For example, I created a procedure to load associated products, no need to create a temp table just to make a simple query, I have an immediate and visible amelioration! ProductLoadAllPaged should be used only for search page.
- Use more store procedures, for public area only! EF is perfect for admin pages, where loading time is not a goal. But for public pages and particularly the catalog, each query should be optimized
- Lazy loading could be avoided in some situations. For my 20 grouped products, each product contains 4 associated products, I have 80 queries SELECT ... FROM [dbo].[Discount_AppliedToProducts] AS [Extent1] INNER JOIN [dbo].[Discount] AS [Extent2] ON [Extent1].[Discount_Id] = [Extent2].[Id]
Proposition : Manually pre load some data, or use include statement in linq queries
- The number of database request should be reasonable and controlled, actually we can have 100 request for just one page!
- Some processes are called twice ore more in the code. Example: in GetDiscountAmount, we call GetPreferredDiscount, and a few lines later we call GetFinalPrice. Both methods are looking for the applied discount.
Proposition: for this example, GetFinalPrice could have a new parameter 'out Discount appliedDiscount' needed in many cases
- More than 90% of data read by EF will never be updated, just loaded to be displayed. And we never use .AsNoTracking() feature, so thousand change tracking objects are instantiated, initialized, reference all objects loaded, then go the garbage collector.
Proposition: Add a boolean readonly parameter to each GetXXXObject, and use .AsNoTracking() on repository
As you can see I am focused on database access, because I know it well, and all these points have a real impact on performance
But I thinks other efforts could be made
- in plugin management to load / unload without restart application
- on injection to avoid so much first chance exception at startup etc.
- etc
I hope the community will give more ideas, I hope dev team will accept to work on this, thank you to them for their patience
Regards,
Nicolas