Hi everyone,

I thought I'd share a solution to a problem we've had with the Commerce R2 ProductPicker, hopefully this will help others as well.

Problem:
The ProductPicker takes a long time to show (a couple of minutes) when it builds the catalog treeview.

Affected systems:
Data size was in our setup (in order to reproduce) approx. 9000 SKUs, each with 150 fields. CatalogNode table row count = 5800 rows.

Identified cause:
Excessive database access by EPiServer.Business.Commerce.UI.dll's ProductPicker class when building the treeview using CatalogContext calls. Unnecessary data for building the treeview is also returned.

Detailed cause:
ProductPicker.GetChildCatalogs(TreeNode) uses the following code to fetch nodes:

CatalogNodes nodes = (node.Depth == 1) ? CatalogContext.Current.GetCatalogNodes(int.Parse(node.Value)) : CatalogContext.Current.GetCatalogNodes(this.GetCatalogID(node), this.GetCatalogNodeID(node));

Profiling SQL Server reveals that approx 70 000 calls are made to the database, typically with nested "SELECT *"-statements, both for node data but also for entry fields, SEO-info etc.

For rendering the treeview, this should not be needed. The treeview only needs to fetch nodes, their relationships (parent nodes) and things like IsActive, StartDate/EndDate and alike.

Resolution:
1. Change <webroot>/Edit/ProductPicker.aspx to inherit from a custom Product picker class ( Inherits=YourNamespace.CustomProductPicker.cs")

2. Use .NET Reflector or similar to grab the original ProductPicker class source code from EPiServer.Business.Commerce.UI.dll and paste into your custom class.

3. Remove the CatalogContext calls listed above from the GetChildCatalogs() method and replace with two new methods:
- GetNodesForCatalog(catalogId)
- GetNodesForCatalogWithSpecificParent(catalogId, parentNodeId)

The first method should fetch all CatalogNode info from the database for a specific catalogId (one DB access for each catalog) and store in memory (member variable, cache or alike).

The second method can then use the already fetched node collection to filter out nodes with specific parent, without querying the database.

(Basically, the SQL for the first method would be:
SELECT * from CatalogNode WHERE CatalogId = @CatalogId ORDER BY SortOrder
and the second would use standard C# to return nodes with matching parentId from the fetched data)

4. Convert the DB resultset into a list of CatalogNodes. Example below, where each DataRow is converted to a CatalogNode:

        private CatalogNode CreateCatalogNodeFromDataRow(DataRow row)
        {
            CatalogNode n = new CatalogNode();               
            n.CatalogId = Convert.ToInt32(row["CatalogId"]);
            n.CatalogNodeId = Convert.ToInt32(row["CatalogNodeId"]);           
            n.DisplayTemplate = row["TemplateName"].ToString();
            n.EndDate = Convert.ToDateTime(row["EndDate"]);
            n.ID = n.CatalogNodeId.ToString();
            n.IsActive = Convert.ToBoolean(row["IsActive"]);           
            n.Name = row["Name"].ToString();           
            n.ParentNodeId = Convert.ToInt32(row["ParentNodeId"]);
            n.StartDate = Convert.ToDateTime(row["StartDate"]);
            return n;               
        }

5. Compile

Expected result:
One database access per unique catalogId only (we had three catalogs in our setup, so that is quite a nice improvement).
The picker should appear almost instantly instead of after a couple of minutes.

Cheers!

Johan Sjöström
johan.a.sjostrom@capgemini.com