Hi,
We've got 2 large online stores that we want to re-platform on Nopcommerce but we've been pulling our hair out with very poor performance when loading a few thousand categories and their products . The site becomes incredibly slow on front end and on the admin area.
Our old sites are also built in Asp .Net and operate with sub second responses, but nopCommerce can take a few minutes to just load the landing page. The admin section is far worse.
On examining with SQL Profiler, we can see a massive SQL “IN” statement being built over and over again to get all the categories with seems to be at the root of the problem, and the Nop team admits that all the categories are loaded in Application Start-up, however from what we can see it almost seems to happen on Session start-up.
We have tried several options that Nop support has given us, including the solution given here https://github.com/nopSolutions/nopCommerce/commit/833bcc3f1da3dfd1b940b9f13b978b69df499ad9, which helped out with a 20% improvement, which is just not enough.
In the general scale of things these are not large databases, and it is not a matter of server resources, as we have a few hundred sites running very quickly on the same server. DB server is totally separate, in the same data centre.
We would really like to hear from anyone who has similar problems, and has found a solution that works as we've been going round in circles for weeks now
Any help would be greatly appreciated!
