Performance issues with 1.90

Hi there, I've just launched a new nopCommerce based site, using build 1.90. We have some performance issues which weren't so noticeable while in development, but are a serious problem on live (and were actually there on development, we just didn't pick up on it!)

Every page that loads, has a 6 or 7 second delay in loading the page, see the Trace below:

Trace Information
Category  Message  From First(s)  From Last(s)
aspx.page  Begin PreInit    
aspx.page  End PreInit  0.00743027395939987  0.007430
aspx.page  Begin Init  0.00745569618485031  0.000025
aspx.page  End Init  0.00758224858187284  0.000127
aspx.page  Begin InitComplete  0.0076015247747968  0.000019
aspx.page  End InitComplete  0.00761465493519428  0.000013
aspx.page  Begin PreLoad  0.00762694700024724  0.000012
aspx.page  End PreLoad  0.0076434295420228  0.000016
aspx.page  Begin Load  0.0076560009721906  0.000013
TopicPage.ascx  Begin OnLoad  6.1681685800849  6.160513
TopicPage.ascx  Begin BindData  6.16866612935443  0.000498
TopicPage.ascx  End BindData  6.22931908943734  0.060653

You can see that between "aspx.page Begin Load" and "TopicPage.ascx Begin OnLoad" there is a 6-second pause. I'm getting the same delay on every page on the site. The page is http://www.ocsmedia.net/about-us, I've implemented an internal redirect via a Server.Transfer to redirect to the topic page, I've tested that this redirect isn't adding anything to to load time, you can see if you go straight to the topic page that the problem is the same: http://ocsdev.onevision.co.uk/Topic.aspx?TopicID=6

Besides reskinning the site, I haven't changed any of the code on the Topic page. Page_Load in Topic.aspx.cs is empty as per the original file, none of the code in TopicPage.aspx.cs is changed from the original.

Obviously my client isn't too happy with this!! If anyone can shed any light on why this would be and what I can do to fix it, it would be much appreciated.

Regards, Ben

Apologies, I found a badly coded query that was causing this problem. It was doing a count on ProductVariants, which for a particular product was around 8000 rows. This was causing the poor performance - I rewrote the query to do a count on the database side, and problem solved :)

Cheers, Ben

A bit more info on this. The problem was with a built-in property to the original nopCommerce, not something we added.

In BusinessLogic.Products.Product.cs, there is a property called HasMultipleVariants. The code in this was:

return (this.ProductVariants.Count > 1);

Obviously, this has to load all variants in order to determine if there's more than 1 or not. Our site has 60000 variants, with several products having 8000+ each. Clearly, this call was massively inefficient in these circumstances!! We also made a tweak that meant this property was getting called on every page (since we have some special products with 1 variant only, and in these cases we need to output different urls in the menus).

I changed this to the following (with a cache also):

            var query = (IQueryable<ProductVariant>)_context.ProductVariants;
            query = query.Where(pv => pv.Deleted == false);
            query = query.Where(pv => pv.ProductId == productId);        
            int prodVarCount = query.Count();

Problem solved :) I would recommend nop to avoid programming in the initial way!

Regards, Ben