Hello all,

 

I am using DotShoppingCart v3.11.

 

I have a Left Master Block that gets Enabled or Disabled depending on what page the user is on.  Inside this Left Master Block, there is a Category List Block.

 

On the pages that have the Left Master Block disabled, the Page_Load code for the Category List Block still gets executed.  This is a big problem because there are thousands of categories, and this is a VERY expensive query against the database.  Is there a way I can either a) Make sure the Category List Block does not get loaded at all when it's containing Master Block is disabled, or b) Have a test within the Page_Load of the Category List Block to determine whether the Master Block is disabled so I don't load the Category data?

 

Any help is appreciated!

-Mark

I don't think Category List Block is all that expensive because the data are cached rather than fetching from database.

 

To check if the Master Block is disabled or not, you can use ((DSCMasterPage)Page.Master).LeftColumnVisible property in Category List code behind.

Thanks for your reply,

 

There doesn't seem to be caching happening on Category List in v3.11.  I've done a fresh install, added a few categories and products, and I can see that it recreates the Categories/Subcategories tree for the Product List on every page hit, even though ProductList is in a disabled LeftMasterBlock for all but a few pages.  Even in this clean install with a few categories and products, it hits the database fairly hard on every page hit because it calls the DSC_Category_Get stored proc many times, which is quite expensive.

 

Also, I found that the LeftColumnVisible property is not even set until all of the UserControls have already been rendered.  So it creates the CategoryList user control, fills it from the database, then sets the Master Page's LeftColumnVisible property within the Page_Load of the PageBlockContainer control.  Again, I've confirmed this from a fresh install of v3.11.

 

I tried to add code to the Default.aspx and Dynamic.aspx page's LoadBlockContainer methods, which sets the Master Page's 'ColumnVisible' properties as soon as we load the Block.  However, I found that on my client's store, it loads the ProductList control twice when the control is actually shown, and the first time 'LeftColumnVisible' is true (Which is correct), but the second time it is false (Which is incorrect)..  I just took over this store from other developers, and was just too frustrated by this point to find out exactly what was going on.

 

In the end, I ended up just filling in the 'dynamic' Category information, like productCount, parentFullPath, and hasChildren right into the Product table, and refreshing the table on a regular basis.. So I don't have to grab that information every time.  This caused the number of query executions per page to fall from around 5000 to around 50, and improved performance significantly.

 

 

Yes, it seems that LeftColumnVisible is set later in the page cycle. Even though Category might be not cached in that version the store procedure DSC_Category_Get should not be all that expensive. Which query was loading multiple times?

The problem is just the sheer amount of Category and Product data that I'm dealing with.  There are approximately 3000 Categories, and almost 10000 Products.

 

The behaviour I was seeing was this:

- The CategoryList control, which loaded on every page, would call DSC_Category_Get for all Categories and at all Levels.

- For each level, DSC_Category_GetByTempTable is called

- For each category inside this proc, DSC_Category_GetParentFullPath is called.

- Inside this proc, DSC_Category_GetParents is called

- DSC_Category_GetParents recursively gets the parents of every category, then returns an in-memory table of the results

- When looking at the SQL Profiler logging just Stored Procedure calls, I was seeing over 5000 rows per page hit.  With a couple of users on the site, I was seeing hundreds of thousands per minute, and database operations started blocking because there was a backlog since SQL Server couldn't keep up..  And this is a very powerful server.

 

The result of most of this work is to get the ParentFullPath, CategoryCount and the HasChildren flag for each category..  But this information is relatively static and certainly does not have to be retrieved on every page hit (Especially considering 99% of the pages on the site have the CategoryList in a disabled LeftMasterBlock and it never gets shown to the user!). 

 

Because after hours of searching, changing and debugging, I still couldn't find a reliable way to refrain from loading the CategoryList if the LeftMasterBlock is disabled, I decided to just optimize the DSC_Category_GetByTempTable proc.  I added ParentFullPath, CategoryCount and HasChildren columns right to the Category table, and just update them periodically through a SQL job.  The proc now just does a simple join between Category and the #subset_Category temp table..  So my number of executions of stored procs per page hit have now dropped from 5000 per page, to around 50..  And the performance improvement of the site now makes it usable. ;)

Ah, 3K categories for 10K products. It seems like too many categories in terms of number of products. Regardless the way to poplulate the parentFullPath has the performance issue. I think other two properties should be fine. hasChilden is one addtional join and categoryCount is via the indexed view "vw_DSC_Category_Count cc WITH (NOEXPAND)".

 

Thank you for reporting back what you found. We will improve the parentFullPath population in the next version.

Thanks. 

 

I think that the biggest performance increase could be gained by not loading User Controls that are in disabled Master Blocks..  Or at the very least, setting the 'MasterBlockVisible' properties on the Master Page BEFORE the Page_Load of the User Controls fire, so at least those properties can be checked to determine whether to load the data or not.

 

In an ideal world, each User Control would have it's own 'VisibleInBlock' property, or something like that, which would indicate if it's currently visible in the block it's contained in.  That way, I wouldn't have to check, for example, 'LeftMasterBlockVisible', because theoretically the user control could exist in a LeftMasterBlock and a RightMasterBlock.. The user control shouldn't have to know what Block it's actually contained in.

 

Hope that helps.

 

Agreed. If the container is invisible the blocks inside the container should be skipped altogether. We will improve this in the next version.