Large catalog timeout generating Sitemap file

For general questions and discussions specific to the AbleCommerce GOLD ASP.Net shopping cart software.
Post Reply
User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Large catalog timeout generating Sitemap file

Post by AbleMods » Tue Sep 06, 2016 2:31 am

I've got a client with 94,000+ products in the catalog, each one has a digital good.

When we run the sitemap generator page, it times out every time. I've increased the timeout from 2 minutes all the way up to 20 minutes. Still, the page times out.

Any suggestions on improving the routine so it will run in a reasonable time? The catalog is large, but it shouldn't take that much time to build a sitemap file.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

User avatar
mazhar
Master Yoda
Master Yoda
Posts: 5084
Joined: Wed Jul 09, 2008 8:21 am
Contact:

Re: Large catalog timeout generating Sitemap file

Post by mazhar » Wed Sep 07, 2016 1:18 am

What happens when feed is generated automatically without manually triggering it from page? In feed there is some code related to price calculation and variants, I wonder if you try commenting out that part obviously just for testing and see if it makes any difference. Is there any error in log ?

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Large catalog timeout generating Sitemap file

Post by AbleMods » Wed Sep 07, 2016 1:42 am

mazhar wrote:What happens when feed is generated automatically without manually triggering it from page?
That was my question too. There is no file being auto-generated. And there's no error in the error log, which is confusing because there should be something there. Nothing in app.log either.

The error is just 'Request timed out.'. It seems like it's running, it's just taking an enormous amount of time to do it.

Is the sitemap generator code in /app_code/ ? I don't have full source for this client.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

rpb3
Lieutenant (LT)
Lieutenant (LT)
Posts: 60
Joined: Fri Jan 23, 2009 11:20 am

Re: Large catalog timeout generating Sitemap file

Post by rpb3 » Fri Aug 11, 2017 1:52 pm

Joe... did you ever figure this out? I have a site doing the same thing all of a sudden. Also has a very large catalog.

rpb3
Lieutenant (LT)
Lieutenant (LT)
Posts: 60
Joined: Fri Jan 23, 2009 11:20 am

Re: Large catalog timeout generating Sitemap file

Post by rpb3 » Fri Aug 11, 2017 5:22 pm

After much investigation... I figured out it was simply too many products in the DB (around 250,000)... so I switched out this code:

Code: Select all

            foreach (Product prod in store.Products)
            {
              if (prod.Visibility == CatalogVisibility.Public)
              {
                url = UrlGenerator.GetBrowseUrl(prod.Id, CatalogNodeType.Product, prod.Name);
                siteMapItems.Add(new SiteMapUrl(GetAbsoluteUrl(url), options.DefaultChangeFrequency, options.DefaultUrlPriority));
              }
            }
With this code:

Code: Select all

            ICriteria criteria = NHibernateHelper.CreateCriteria<Product>();
            criteria.Add(Restrictions.Eq("VisibilityId", (byte)CatalogVisibility.Public));
            IList<Product> _Products;
            _Products = ProductDataSource.LoadForCriteria(criteria); 
            foreach (Product prod in _Products)
            {
                url = UrlGenerator.GetBrowseUrl(prod.Id, CatalogNodeType.Product, prod.Name);
                siteMapItems.Add(new SiteMapUrl(GetAbsoluteUrl(url), options.DefaultChangeFrequency, options.DefaultUrlPriority));
            }
in App_Code\CommonSiteMapProvider.cs

It wasn't a database timeout or bad product or circular reference etc. I think the page was timing out waiting for the code behind to run.

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Large catalog timeout generating Sitemap file

Post by AbleMods » Mon Aug 14, 2017 1:17 am

Interesting. Did you have a lot of products where visibility was not set to public?

I almost wonder if the difference was in how you're calling LoadForCriteria() directly as opposed to how nHibernate populates the child collection of store.Products. I wouldn't expect them to be any different in terms of performance, but perhaps it is.

250,000 is quite the catalog size :)
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

rpb3
Lieutenant (LT)
Lieutenant (LT)
Posts: 60
Joined: Fri Jan 23, 2009 11:20 am

Re: Large catalog timeout generating Sitemap file

Post by rpb3 » Wed Aug 16, 2017 2:38 am

Yes, he has many products that are not public. Product samples, fabric remnants, etc. I probably need to get with him and remove some if possible but he might want to save the order history on them. Would have to look into that.

I was wrong on the product count. ProductId is up to 238K but only 168K actual records. Only 42,000 are public, 84,000 are hidden and 42,000 are private.

So pulling it the way I am now I cut it down to about 25% of the total.

User avatar
AbleMods
Master Yoda
Master Yoda
Posts: 5170
Joined: Wed Sep 26, 2007 5:47 am
Location: Fort Myers, Florida USA

Re: Large catalog timeout generating Sitemap file

Post by AbleMods » Wed Aug 16, 2017 4:05 am

That's what I figured.

You might gain a little boost to query speed if you added an index on the VisibilityId field in ac_Products. Might not help much, but easy to at least try and see.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com

Post Reply