What is maximum number of products?

[zafu]

Fred asked me to post this here. We are evaluating ecommerce packages.

I imported 349,458 products with the DataPort tool into the AbleCommerce trial. It took 4 hours but completed successfully. No images. No extended description. Browsing the site caused several time out errors.

We would have just moved on, but supposedly other sites (http://www.betterworld.com) are using AC (version?) with more products. Are those highly customized implementations or are we missing a special configuration on our trial? What is your recommendation for our next step?

TECHNICAL DETAILS
Test system: Dell Optiplex755, Intel Core2 Duo 3GHz, 3.25GB RAM; Windows XP Pro SP3; IIS 5.1; SQL 2005 Express
AbleCommerce DB mdf file size: 2.7GB


ADMIN SIDE ERRORS

1. After entering log in info and clicking “SIGN IN” (page: http://localhost/able/Admin/Default.aspx)
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

CUSTOMER SITE ERRORS

2. Trying to view the home page took about a minute to load the page then displayed the following error where the featured products should have been (http://localhost/able/Default.aspx):
[[ConLib:FeaturedProductsGrid]] Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

3. Clicking the product finder button (page: http://localhost/able/Search.aspx) took a minute before the following error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

4. Clicking Advanced Search button (page: http://localhost/able/AdvancedSearch.aspx) brought the page up right away, but then searching took a minute before:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

5. From the home page clicking a category that contains over 100,000 products resulted in (http://localhost/able/Software-C17.aspx):
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

WORKAROUNDS?

We’ve seen these problems before when we tried to modify our customer’s original shopping cart. We’d rather not increase the timeout value since visitors to the site would lose patience and leave the site.

1. Not being able to log in to the admin side is a biggy and we would rather not mess with this code.
2. Rewriting the query, adding an index to table, caching product IDs. We wrote similar featured product module for an asp page that loaded in less than a second.
3. We’re not sure why simply showing the search page without search results should cause a timeout.
4. Convert search to use full-text (by building a full-text catalog in sql server admin then replacing LIKE in search queries with FREETEXT or CONTAINS)
5. We were planning to split the Software category by manufacturer anyway, but this might still be a problem since AbleCommerce aggregates products in sub categories when a parent category is clicked. It handled the Networking category which has 33,693 products ok, so we might just modify the query to return the top 30,000 products.


FYI other products we’re evaluating
ASPDotNetStorefront (advertised 250,000 products max)
DotShoppingCart (Sales dept. claims it can handle 1,000,000 products; we haven’t tested yet with our catalog)
LaGarde Storefront (Weren't very confident above 6,000 products; they recommended their SAAS solution Phoenix)
Znode (Similar customer site bottlenecks; admin side is functional though slow on a few pages)

[zafu]

Here is the products CSV file I used to import products. http://drop.io/ablecommerce (I had trouble with file upload)

[afm]

I'm thinking SQL Express is the culprit. It can only use 1 processor in your dual core and 1 GB your memory. Unfortunately, there is no profiler in SQL Express so it is hard to know for sure. Is there any chance you could mount the db on a non-Express SQL Server just to retest the timeouts?

[jmestep]

I would guess SQL Express also, plus the fact that you are not on a real server, just an XP Pro box. If the site you referenced is still an Able site, it is highly customized- even the page names are different from the normal Able checkout page names.

[AbleMods]

Of course it's going to time out.

You're using the single weakest operating system (barely) available today, combined with the most limited version of Microsoft SQL available today to test a huge amount of data. All that's going to do is set you up for an amazing failure of performance results. If you've done all your ecommerce performance testing on this platform, toss everything you've learned thus far. None of your testing platform will be used in your live environment, why use the results from it as a basis for comparison?

To effectively test the performance of such a large storefront deployment, you're going to have to accomplish all your testing far closer to the production hardware platform. WinXP and SQL 2005 Express have no where near the data throughput as compared to, say a dual-CPU Quad-Core Xeon running 15k drives in a RAID-5 setup with a separate physical database server also running RAID-5 tied together with a 1Gb private switch.

This also means DiscountASP or other low-ball .Net hosting solutions aren't going to fly either. They'll never give you the kind of data and CPU throughput it takes for an IIS app to drive that large of SQL db for a mere $9.95 a month. You're going to need to own your own server hardware or pony up the cash for some VPS instances.

Use your current testing environment for *functionality* testing only, and just on a small (< 5,000) subset of the catalog you want. Once you have the functionality needs reviewed, then decide how much you're willing to fork out for hardware. The more you spend on hardware, the faster everything will go. In your case, you're going to want at least 2 servers, two Win2K3 licensese and a full license of SQL.

AC7 itself can handle millions of products, but how effectively it performs with millions of products is moreso decided by the hosting hardware involved. This will be the case for any ecommerce package. 300,000 records in a single table isn't all that much. But there are dozens of tables all associated with each other via indexes, foreign keys, relationships etc.

And finally, your site design will have the biggest impact on overall performance. If you slap 5 different user controls on one page, each control tags the DB for a query. That page will slap the db 5 times for every page hit. 50,000 hits a day and now you're looking at some real DB performance considerations. So a really slow performing site could easily be faster just by reducing the amount of db data required to render the page.

[zafu]

Thanks for all the suggestions.

I agree faster hardware and a server OS would perform better. I was looking to test relative performance on my dev box for initial testing.

The same 350,000 products in ZNode running on same hardware didn't cause any timeouts on Admin side (although slowest page took 60-80 seconds to load) and customer site only timed out trying to view categories that had more than 4,000 products (AC handled 30,000). Search timed-out on both, but we will be using full-text anyway. Znode sales didn't give any guarantees beyond 50,000 products so we expected fewer time-outs from AC on the same system.

Perfmon showed the bottleneck on my XP system to be disk access (processor and memory had spikes, but sustained usage under 50%). Database MDF file is on a local Western Digital 160GB SATA2 10K HDD (separate physical drive from OS and virtual memory; defraged; 50% free space).

We do like AbleCommerce's feature set and will go ahead and test on new dual-CPU Quad-Core Xeon servers w/ Win2003 and enterprise edition of SQL Server 2005.

[afm]

I was looking to test relative performance on my dev box for initial testing.

The danger with testing on a dev box is that the constraints you impose can negatively affect the products in ways that spoil the tests.

As a simplistic example, let's say product A is designed to retrieve data from the product and category tables and then perform a join in code; while product B performs the join in SQL Server. Product A may perform equal to or possibly better than product B on your dev box (where SQL Server is artificially constrained) for some number of products and categories, while product B will scale up better and will really shine when you use a separate, well tuned, db server.

In other words, what you have been testing is how the various products perform within an artificial set of constraints. Unfortunately, the test results do not tell you very much about how the products will perform in the target environment, and I worry that if you make a decision based on your current testing, you will be sorely disappointed when you implement the solution in your target environment.

My gut feeling is that 350,000 products is not even close to the practical limit for AC7 on typical production servers, but it is well over the practical limit for your dev box.

[Shopping Cart Admin]

Hello,

There are a couple of areas that need to be tuned for large catalogs. The category trees on the admin and store side need to show only the top level 'categories' otherwise you'll overload the browser with data and possibly timeout slower servers. We've tested on 40,000 categories and 1 million products. Those were the issues we'd found. Basically you just have to design the site without trying to send the client 40,000 categories on each page load.

Running the sql tuning wizard is always a good idea as a site grows and the data becomes more mature, adding indexes as suggested can make a huge difference in site performance.

[zafu]

There are a couple of areas that need to be tuned for large catalogs.

Thanks for the tip. I can understand that AC7 (like most ecom packages) is optimized for average size catalogs, which are smaller than 350,000 products. I was able to tune most problem areas, however execution time for some of the queries* could not be improved with index(es). Would you work with us to change these queries/code or would we need to purchase the AbleCommerce API source code and work it out on our own?

Thanks



* FOR EXAMPLE: when clicking a product to view product details the following query takes about 2 minutes and returns 250,000 records. I assume it is used as the dataset to pull the 4 products for the "More Items in <Category>" at the bottom of the product detail page. If we could simply remove that control, great, but I'm also thinking ahead in case there is no such workaround for this or another problem area.

exec sp_executesql N'SELECT node.CatalogNodeId, node.CatalogNodeTypeId, node.OrderBy, child.VisibilityId, child.Name, child.Summary, child.ThumbnailUrl, child.ThumbnailAltText, 0 AS Price FROM ac_CatalogNodes node, ac_Categories child WHERE node.CatalogNodeId = child.CategoryId AND node.CatalogNodeTypeId = 0 AND node.CategoryId = @categoryId AND child.VisibilityId = 0 UNION ALL SELECT node.CatalogNodeId, node.CatalogNodeTypeId, node.OrderBy, child.VisibilityId, child.Name, child.Summary, child.ThumbnailUrl, child.ThumbnailAltText, child.Price FROM ac_CatalogNodes node, ac_Products child WHERE node.CatalogNodeId = child.ProductId AND node.CatalogNodeTypeId = 1 AND node.CategoryId = @categoryId AND child.VisibilityId = 0 UNION ALL SELECT node.CatalogNodeId, node.CatalogNodeTypeId, node.OrderBy, child.VisibilityId, child.Name, child.Summary, child.ThumbnailUrl, child.ThumbnailAltText, 0 AS Price FROM ac_CatalogNodes node, ac_Webpages child WHERE node.CatalogNodeId = child.WebpageId AND node.CatalogNodeTypeId = 2 AND node.CategoryId = @categoryId AND child.VisibilityId = 0 UNION ALL SELECT node.CatalogNodeId, node.CatalogNodeTypeId, node.OrderBy, child.VisibilityId, child.Name, child.Summary, child.ThumbnailUrl, child.ThumbnailAltText, 0 AS Price FROM ac_CatalogNodes node, ac_Links child WHERE node.CatalogNodeId = child.LinkId AND node.CatalogNodeTypeId = 3 AND node.CategoryId = @categoryId AND child.VisibilityId = 0 ORDER BY OrderBy',N'@categoryId int',@categoryId=29


=========================================================
Tuning details for large product catalogs in case others find it useful.

Catalog used:
14 root level categories
105 2nd level categories
349,458 products
Largest category (Software > Applications) with 257,815 products
We are constrained by the products and categories as they are the default from TechData (an electronics wholesaler for retailers such as CDW). Our client desires this structure for their initial catalog.



>> Logging into admin dashboard
>>> Original query (31 seconds)
SELECT COUNT(*) AS SubTotal FROM ac_Products WHERE InStock <= InStockWarningLevel AND InventoryModeId = 1
>>> Added index on ac_Products.InventoryModeId


>> Displaying home page
>>> Original query (24 seconds)
exec sp_executesql N'SELECT P.ProductId FROM ac_Products P WHERE P.StoreId = @storeId AND P.VisibilityId = 0 AND P.IsFeatured = 1',N'@storeId int',@storeId=1
>>> Added index on ac_Products.IsFeatured


>> On customer site, browsing parent category that has many products (250,000)
>>> Original query (2:18 minutes)
exec sp_executesql N'SELECT TOP 15 P.ProductId,P.StoreId, P.Name,P.Price, P.CostOfGoods, P.MSRP,P.Weight, P.Length, P.Width,P.Height, P.ManufacturerId, P.Sku,P.ModelNumber, P.DisplayPage, P.TaxCodeId, P.ShippableId,P.WarehouseId, P.InventoryModeId,P.InStock,P.InStockWarningLevel, P.ThumbnailUrl, P.ThumbnailAltText,P.ImageUrl, P.ImageAltText,P.Summary, P.Description, P.ExtendedDescription, P.VendorId, P.CreatedDate,P.LastModifiedDate, P.ProductTemplateId, P.IsFeatured, P.IsProhibited, P.AllowReviews, P.AllowBackorder, P.WrapGroupId, P.ExcludeFromFeed, P.HtmlHead, P.DisablePurchase, P.MinQuantity, P.MaxQuantity, P.VisibilityId, P.Theme, P.IconUrl, P.IconAltText, P.IsGiftCertificate,P.UseVariablePrice,P.MinimumPrice, P.MaximumPrice, P.SearchKeywords, P.HidePrice FROM ac_Products P WHERE P.ProductId IN (SELECT DISTINCT P.ProductId FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE P.StoreId = @storeId AND PC.CatalogNodeTypeId = 1 AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId) AND P.VisibilityId = 0) ORDER BY Name ASC',N'@storeId int,@categoryId int',@storeId=1,@categoryId=17
>>> Added index on ac_Products.Name


>> Admin "error log" page time-out.
>>> Not sure when the error log got filled up. Original data was imported with AC Data Port utility 9/19/2008. Log also included error entries for sample data. (Tables allow NULL values and rest of application not affected by NULL fields.)

>>> Message in Admin dashboard
There are 3,844,959 messages in your error log. You should review these messages and take corrective action if necessary.

Example entries:
1 56:14.2 1 3 Error in DataObject.ConvertToAC6Object() : AC6Type: CommerceBuilder.Catalog.Category the value for srcProperty ofSummary is null NULL
834 13:50.0 1 3 Error in DataObject.ConvertToAC6Object() : AC6Type: CommerceBuilder.Products.Product the value for srcProperty ofSku is null NULL

>>> Deleted all in SQL managment studio.

[AbleMods]

Fantastic information, thanks for sharing with the community.

TechData DataPaq is absolutely enormous. I've considered attempting it with an AC7 storefront for several months now. But the development effort to integrate it all into a usable visitor frontend would be far more than my schedule can handle by myself.

Then of course there's the whole going head-to-head-with-a-Fortune-500-company thing

If you guys need any contract help, I'm extremely IT saavy (25+ years) and very fluent in AC7 development. I've done extensive work in data import/update routines on my Solunar.com site. I've also been a TechData reseller for 8+ years. Feel free to PM me if you have need for my services.

[Logan Rhodehamel]

I know it's already old news but I will just chime in agreement. I was doing some load testing this week. When I moved an install from SQL Express to SQL Server, the performace limitations of Express were more than obvious.

You are probably correct, we do not test specifically for so many products. But at one time I did create a million product store to try and resolve issues. Perhaps I will do that again to see where the pain spots might be. I have downloaded your CSV and will let the import run tonight on a test install.

I do not see a big reason it should not be able to deal with that number of products, given some adjustments. The biggest issue is search, which you have already noted needs to be based on a full text catalog.

[zafu]

For a single user, which is only testing we did, moving from a dev box to a production system didn't make much difference (all the time-outs remained). Adding those 3 indexes I posted earlier fixed 80% of the bottlenecks on production and on dev systems. I was impressed that after those db changes AC7 could handle 350,000 products on XP pro and SQL 2005 express as well as it did.

[zafu]

I have downloaded your CSV and will let the import run tonight on a test install.

The problem with the Applications category (as you'll see) is that it contains 250,000 products so when you click to view a product's detail page, sql server has to return all 250,000 records to the web app so that it can choose 4 random products to display at the bottom of the product detail page.

I admit 250,000 products in one category isn't very practical, but that's what we have to work with from TechData.

[zafu]

I have downloaded your CSV and will let the import run tonight on a test install.

Any luck?

We are still having trouble. The Software > Application category with 250,000 products was timing out. We created 13 subcategories and moved products into each (14,000 to 26,000 products per category). Each of those categories display products, but other categories that had been displaying time out now. Queries that were running < 1 sec now take 45 seconds.

--
details:

The problem query is

exec sp_executesql N'SELECT TOP 15 P.ProductId, P.StoreId, P.Name, P.Price, P.CostOfGoods, P.MSRP, P.Weight, P.Length, P.Width, P.Height, P.ManufacturerId, P.Sku, P.ModelNumber, P.DisplayPage, P.TaxCodeId, P.ShippableId, P.WarehouseId, P.InventoryModeId, P.InStock, P.InStockWarningLevel, P.ThumbnailUrl, P.ThumbnailAltText, P.ImageUrl, P.ImageAltText, P.Summary, P.Description, P.ExtendedDescription, P.VendorId, P.CreatedDate, P.LastModifiedDate, P.ProductTemplateId, P.IsFeatured, P.IsProhibited, P.AllowReviews, P.AllowBackorder, P.WrapGroupId, P.ExcludeFromFeed, P.HtmlHead, P.DisablePurchase, P.MinQuantity, P.MaxQuantity, P.VisibilityId, P.Theme, P.IconUrl, P.IconAltText, P.IsGiftCertificate, P.UseVariablePrice, P.MinimumPrice, P.MaximumPrice, P.SearchKeywords, P.HidePrice FROM ac_Products P WHERE P.ProductId IN (SELECT DISTINCT P.ProductId FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE P.StoreId = @storeId AND PC.CatalogNodeTypeId = 1 AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId) AND P.VisibilityId = 0) ORDER BY Name ASC',N'@storeId int,@categoryId int',@storeId=1,@categoryId=4

The query takes 45 seconds. The category contains 9 products. I have an INDEX to P.Name, but does not help in this case. It will run in less than 1 second if I remove the "ORDER BY" clause or if I remove one of the nested queries and leave the ORDER BY. Individual queries run fine, but nested in the way they are chokes.

Since Able has tested with 1,000,000 products, I assume problem is with my category structure. How many products max can I have per category so that pages don't time out? How many subcategories per category? BTW, admin also times out when I try to delete a category with too many products in it (even if the category page displays in the store).

[Logan Rhodehamel]

You may have better luck if you go to Website -> Themes and Display Pages. Change the default category display page to something other than the deep item display grid. That is choking on the number of products in the applications category.

When we tested a million products, they were fairly evenly distributed among categories.

[zafu]

Change the default category display page to something other than the deep item display grid.

Thanks for tip--will try. We like the deep categories since none of our 1st level categories contains products. Maybe there is another workaround since the results are unusual*: the applications category is traversable while smaller ones are not.

For example, speed of displaying certain categories at our client's site...

http://www.compzoo.com

-Software =slow=
--- Applications (252273) =slow=
------ Tier N (about 20,000) =little faster=*
--- GPS Software (20) =time out=
--- Operating Systems (5217) =fast=


-Cameras =fast=
--- Camcorders and Digital Cameras (276) =slow=
--- Film (42) =slower=
--- Filters (15) =time out=*
--- Flashes (21) =time out=*
--- Lenses (225) =fast=
--- Tripods (9) =time out=*
--- Web Cameras (654) =fast=

Any suggestions would be appreciated-- db tuning, store configuration, or source code related (we had to buy source code to get other parts working such as search and creation of site maps and product feeds).

[Logan Rhodehamel]

In this case, the big problem is the ProductDataSource.NarrowSearch, which you originally suspected and I also confirmed with trace. I looked at this query but I could not quickly improve it to deal with such a large recordset. I have registered a bug so that we can investigate this issue as part of our next development cycle.

In the meantime, since you have source you may be able to solve the problem more quickly. What I would look at doing is either creating a SQL stored procedure to mimic the function of NarrowSearch and/or creating a view to eliminate the need for ad-hoc joins in the query. In order to provide all the criteria we have to join up three tables. A view within SQL Server would be more efficient and could also be indexed.

[zafu]

I put this on hold for now. The execution path SQL Server creates seems a little screwy. The query runs fine if the inner most subquery returns more than 20 or so records, otherwise it takes many minutes.

A stored procedure isn't any better (its already a parameterized query) and view doesn't work in this case.

I removed all the subqueries and just used a flat query with multiple table joins. This worked on the problem categories, but results for all the other categories slowed down.

Another option I tried was moving the 'order by' and 'top 15' to the inner subquery so the outer query was smaller. It works, but I have to figure out how to include the DISTINCT. Maybe WHERE EXISTS instead of IN ...

[Logan Rhodehamel]

A stored procedure isn't any better

By that I don't mean take the query as is and put it into a stored procedure. I meant to break the query down into pieces - from within the context of a stored procedure it could be done more efficiently than a single query. Actually if you look at the code, to some extent we are doing processing via code that could be done inside a stored procedure. If the start row and record count were additional parameters to a stored proc, the query could return ONLY the records needed for display.