Super slow after loading 15,000 products - no images

Started by epimadman, May 03, 2016, 12:40:29 AM

Previous topic - Next topic

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
Please  rate your experience or leave your review
We need your help to build better free open source ecommerce platform for everyone. See how you can help

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.
Please  rate your experience or leave your review
We need your help to build better free open source ecommerce platform for everyone. See how you can help

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

Quote from: dcart2 on May 04, 2016, 11:45:50 AM
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.

Quote from: dcart2 on May 04, 2016, 11:45:50 AM
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

Quote from: dcart2 on May 04, 2016, 11:45:50 AM
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 #
Please  rate your experience or leave your review
We need your help to build better free open source ecommerce platform for everyone. See how you can help

abantecart

Quote from: dcart2 on May 04, 2016, 03:17:47 PM
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.
Please  rate your experience or leave your review
We need your help to build better free open source ecommerce platform for everyone. See how you can help

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

Please  rate your experience or leave your review
We need your help to build better free open source ecommerce platform for everyone. See how you can help

Forum Rules Code of conduct
AbanteCart.com 2010 -