Performance optimization needed - NOPCOMMERCE IS TOO SLOW !

Follow-up to this post: I basically got my SearchProducts code from this SQL query:

http://pastebin.com/AHknbM99

Down to this


Much neater IMO, and saves valuable milliseconds from not having to transfer excess data from the database.

sproaty wrote:

Very interesting suggestion, you are pointing another database access optimization. Impact on development is very important, but the result is here...

My performance tuning has been of a result of seeing how many queries were being executed by using Glimpse and not being too impressed by the results. The controllers were doing far too much for what I needed and as we have such specific use cases for product listing pages then a re-write was much easier.

A custom Route we use that catches all URLs {*url} parses the URL into its relevant pieces and populates an object that containns the matched Category, Manufacturer, price ranges, sort options etc etc. This object is then passed to the controller which then calls a couple of service methods to retrieve further filtering information for that page (other brands, subcategories, gender, price ranges) and transforms then into relatively-small view models.

So the Action method of the controller itself is about 30 lines of code, with a couple of private methods for creating the view models. Any view logic is pushed into these view models; absolutely no logic in the views.

This takes about 6 database calls, all of which are heavily optimised to not fetch too much data and to basically push as much data set querying (distinct rows, row counts, grouping etc) to the database rather than doing it in code.

a.m. wrote:

This is the most optimal approach and is what I do when I develop custom made financial ASP.NET applications by using this approach:

EF for modeling and every DB task that is not subject to high traffic

Dapper and SPs for every DB task that is subject to high traffic

This way you end up with the best of both worlds: Fast coding and changes with EF, high performance only where it's absolutely needed with Dapper.

ChuckR wrote:

The current Nop Commerce is not EF dependent per se, there is a model layer and there is an IRepostry interface that gives the implementation flexibility in it's dependence from EF, additionally in the bottleneck that we're talking about the code is using EF to call the SPs, much as Dapper does more efficiently, so moving this performance sensitive code from EF to Dapper really won't be a problem.

As I said before, EF is very important because it brings fast coding and changes and it makes the 95% of the plumbing code faster to write and mantain, but it's really way overdue to tune the parts where the bottleneck SPs are called.

progresivo wrote:

I absolutly agree on this point, hight traffic parts should be treated with more care.

progresivo wrote:

I don't think is necessary, my posts above show "high traffic" pages rendering in < 300ms using pure EF queries.

Finally Updated to Nopcommerce 3.3 as i was facing performance issue with 3.2 version.

But result are worst. In my case i was getting better performance from 3.2 version.

Let me know you that.
I have 2100 product categories and 2,50,000 Products.

My page is taking 1-2 mins to load.

While doing some reading up on EF and AutoMapper I came across this blog post by Jimmy Bogard, which has some interesting techniques for reducing the number of DB queries and the data it returns, especially for cases where we only read from the database.

govi2010 wrote:
I've just tested the default installation with 10K categories and 100Kproducts. Page load is about 1 second. Please share your database backup and provided a complete list of steps to reproduce the issue