Super slow after loading 15,000 products - no images

[epimadman]

We installed the newest version of abantecart and all we did is import about 15k products with no images yet.  Right now, the speedtest is horrific at size
971.8kB
Load time
29.11s
Requests
90

And the server is throttled wide open, memory, time executions, etc.  Its not the server, its a quad core monster.  Any suggestions?  I hate to start adding images as the page will never load, lol.  29 seconds is really bad, the same products with images on oscommerce was pulling 1.8 seconds..?  ((ps Abantecart is WAAAAAY better than osc so I am always faithful here...!))

site is royalty pet supply dot com

[abantecart]

Can you pm me an access to your hosting/server? I will investigate.

Note: Upcoming version 1.2.7 is related to serious optimization and it will be really helpful if we can check your case.

Thank you

[epimadman]

What type of access? I can post php.ini info or other info or message confidential info, but were you wanting root access or ?  PM me if thats possible, thanks.

[epimadman]

I think we figured out the bulk of the issue, for some reason the import created 1500+ categories (top) when it should be 14 with subcats.  We are reimporting and testing and retesting.  Will post speedup results here.  gzip set at 6 and cache turned on so we are squeezing what we can out of it.  We also have a server with php7 (cart works on fine btw) but will import into that too to see if we can narrow speedtests from each.

thanks!

[abantecart]

Thank you epimadman. Let us know what you ind out.

Good news on PHP 7, but I think we need to spend more time on testing complete solution on PHP 7.

[dcart2]

To be clear about the category structure, there are 8 parent=0.
Of those there are a total of 16 immediate child categories.
Now those branch off and there are a total of 941 rows in `categories`.

I'm certain that this is the issue as I did truncate `categories` and `category_descriptions` and the site loaded speedily. Once the `categories` table got up to > 250 records, the site started to crawl.

[dcart2]

The cart that is having issues is not running on php7, but rather php5.4 with ABCart 1.2.6. We installed another cart on php7 that seems to be working fine.

I was looking at the `getCategory() in `storefront/model/catalog/category.php` and changed the $limit var from the coded `$limit=0` to various other numbers. At $limit=400, the site loads nicely, but no menus past 'Home' are shown. When I raise $limit=600, the first of the store's menu shows, but no children, and at $limit=800, the site starts to crawl again, and even reporting an error (url-encoded in subject line to help-at).


I run the query on the db directly and no issues:

SELECT *
FROM categories c
LEFT JOIN category_descriptions cd ON (c.category_id = cd.category_id AND cd.language_id = '1')
LEFT JOIN categories_to_stores c2s ON (c.category_id = c2s.category_id)
WHERE c.parent_id = '6' AND
     c2s.store_id = '0' AND c.status = '1'
ORDER BY c.sort_order, LCASE(cd.name)

Results in 6 rows in 49ms

OR

SELECT *
FROM categories c
LEFT JOIN category_descriptions cd ON (c.category_id = cd.category_id AND cd.language_id = '1')
LEFT JOIN categories_to_stores c2s ON (c.category_id = c2s.category_id)
WHERE c2s.store_id = '0' AND c.status = '1'
ORDER BY c.sort_order, LCASE(cd.name)


Results in 831 rows in 61ms

Any thoughts?

BTW, when I surround any of my message with square-bracket-code-square-bracket, the forum thinks I'm putting in external links and won't allow me to post the reply.

[dcart2]

Ok, I played with the storefront/model/catalog/category.php model, specifically getCategories(). I left the params as coded ($parent_id = 0, $limit=0) but changed the query to make sure that it would not grab the entire categories table (834 rows) but rather just 24 rows:
$parent_id = 0
and any that has
$parent_id IN (the_list_of_parents)

Store loads much quicker, and the `ul.categorymenu` displays and works correctly. That's all that's needed on the homepage. However, when traversing to `rt=product/category&path=4_662` the `div.contentpanel` loads 24 sub-categories. I'm pretty sure the 24 number is coincidently tied to my code change.

(I just hard-coded my parent_id=0 category_id's just to confirm working, fyi)
SELECT *
FROM categories c
LEFT JOIN category_descriptions cd ON (c.category_id = cd.category_id AND cd.language_id = '1')
LEFT JOIN categories_to_stores c2s ON (c.category_id = c2s.category_id)
WHERE c2s.store_id = '0' AND c.status = '1' AND
      ( c.category_id IN (1,3,4,6,7,8,10,13) OR c.parent_id IN (1,3,4,6,7,8,10,13) )
ORDER BY c.sort_order, LCASE(cd.name)

So, this helps, but is not my answer. More to come later.

[abantecart]

The cart that is having issues is not running on php7, but rather php5.4 with ABCart 1.2.6. We installed another cart on php7 that seems to be working fine.

We are working our way into PHP 7.

I run the query on the db directly and no issues:

SELECT *
FROM categories c
LEFT JOIN category_descriptions cd ON (c.category_id = cd.category_id AND cd.language_id = '1')
LEFT JOIN categories_to_stores c2s ON (c.category_id = c2s.category_id)
WHERE c.parent_id = '6' AND
     c2s.store_id = '0' AND c.status = '1'
ORDER BY c.sort_order, LCASE(cd.name)

Results in 6 rows in 49ms

OR

SELECT *
FROM categories c
LEFT JOIN category_descriptions cd ON (c.category_id = cd.category_id AND cd.language_id = '1')
LEFT JOIN categories_to_stores c2s ON (c.category_id = c2s.category_id)
WHERE c2s.store_id = '0' AND c.status = '1'
ORDER BY c.sort_order, LCASE(cd.name)


Results in 831 rows in 61ms

Any thoughts?

First query has limit to category ID, that is my it brings less in the result

BTW, when I surround any of my message with square-bracket-code-square-bracket, the forum thinks I'm putting in external links and won't allow me to post the reply.

Try to use "Insert code" button with #

[abantecart]

Ok, I played with the storefront/model/catalog/category.php model, specifically getCategories(). I left the params as coded ($parent_id = 0, $limit=0) but changed the query to make sure that it would not grab the entire categories table (834 rows) but rather just 24 rows:
$parent_id = 0
and any that has
$parent_id IN (the_list_of_parents)

Store loads much quicker, and the `ul.categorymenu` displays and works correctly. That's all that's needed on the homepage. However, when traversing to `rt=product/category&path=4_662` the `div.contentpanel` loads 24 sub-categories. I'm pretty sure the 24 number is coincidently tied to my code change.

(I just hard-coded my parent_id=0 category_id's just to confirm working, fyi)
SELECT *
FROM categories c
LEFT JOIN category_descriptions cd ON (c.category_id = cd.category_id AND cd.language_id = '1')
LEFT JOIN categories_to_stores c2s ON (c.category_id = c2s.category_id)
WHERE c2s.store_id = '0' AND c.status = '1' AND
      ( c.category_id IN (1,3,4,6,7,8,10,13) OR c.parent_id IN (1,3,4,6,7,8,10,13) )
ORDER BY c.sort_order, LCASE(cd.name)

So, this helps, but is not my answer. More to come later.

Thank you dcart2!
We are reworking category building to be more efficient. I can update you with the new code a bit later.

[dcart2]

Great.
Would love to test it out.

This forum's error message: "Sorry, you are not allowed to post external links." happens with putting in [ code ] [ / code ] by hand or using the #button

[abantecart]

Feel free to help.

http://forum.abantecart.com/index.php/topic,4652.0.html

Thank you