admin/categories.php running super slow...

Hey all. I have a store with roughly 30K products in it. The front end runs a bit slow, like 2-3 seconds to load up. Im cool with that, its not a big deal.

But the admin panel is just ridiculously slow. It takes about a minute to access the categories.php page and a minute to do anything on that page. Any idea what I can do to speed this up? Is there a reason this page is running so slow? has anyone optimized this page before?

Any help would be great. Thanks.

Much of the code enhancements have been for the frontend leaving the admin side as legacy code.
If yours is taking 3 sec's to load the front, you are on a slow server and will have to contend with the even slower admin side - there are a few admin settings that may help like cat counts off etc

How many Categories do you have? If you have a lot, turning off the Categories Dropdown for the Go To will help a great deal ...

There are a number of customizations that can speed up the Admin considerably ...

Usually, going through the categories.php and products.php and a few other files in the admin and commenting out a few of the bells and whistles can take a 200 second page down to less than 1 second ...

Is there a post that tells what bells and whistles to comment out? We are suffering in that categories.php page (it is so slow we are tearing our hair out).

Ajeh:

How many Categories do you have? If you have a lot, turning off the Categories Dropdown for the Go To will help a great deal ...

There are a number of customizations that can speed up the Admin considerably ...

Usually, going through the categories.php and products.php and a few other files in the admin and commenting out a few of the bells and whistles can take a 200 second page down to less than 1 second ...

What version of Zen Cart are you using?

How many Categories do you have?

How many Products do you have?

Thank you, Ajeh...

1.3.8a
262 categories (master and subcats... we have removed some of them so it really isn't that many... many of these are not master categories they only hold linked products)
Products: 8277
Products deactivated: 982

Ajeh:

What version of Zen Cart are you using?

How many Categories do you have?

How many Products do you have?

Couple things to do here to speed things up ...

Get a download of v1.3.9e ...

Compare the files:
/includes/functions/featured.php
/includes/functions/specials.php

Do you use SaleMaker?

Next, look at the file:
/admin/includes/modules/category_product_listing.php

Test turning off the Category Dropdown list and see if it helps with speed ...

Change the line around 47 from:

  if ($_SESSION['display_categories_dropdown'] == 0) {

to read:

  if (false && $_SESSION['display_categories_dropdown'] == 0) {

To turn off the test for Linked Products which is a heavy load, go down to around line 165 and change:

      if (zen_get_products_to_categories($categories->fields['categories_id'], true, 'products_active') == 'true') {
        echo '  ' . zen_image(DIR_WS_IMAGES . 'icon_yellow_on.gif', IMAGE_ICON_LINKED);
      }

to read:

      if (false && zen_get_products_to_categories($categories->fields['categories_id'], true, 'products_active') == 'true') {
        echo '  ' . zen_image(DIR_WS_IMAGES . 'icon_yellow_on.gif', IMAGE_ICON_LINKED);
      }

I would first try the changes to the specials.php and featured.php ... then try the other changes ...

See if you need more than that for speed ...

NOTE: v1.3.9e is out and much faster on several things ...

If you do not upgrade, make sure you have all Security Patches and Updates:
http://www.zen-cart.com/forum/showthread.php?t=131115

and that you have added all Known Bug Fixes:
http://www.zen-cart.com/forum/showthread.php?t=82619

Looks like a great improvement! Thank you!

We do use salemaker about once a month. I cleaned out that table so there is only one sale in there now (i duplicated the table before i did that to save what we had done previously)

Are there queries (that are already out there) that will look through either products or orders and show if there are orphaned lines in the database? Recently when a person was adding a product, it failed a few times due to how she formatted her html for the description. That left us with about 20 products to clean out of the database. I suspect that might have caused us a problem in the product table (and subsequently the related tables)

Ajeh:

Couple things to do here to speed things up ...

Get a download of v1.3.9e ...

Compare the files:
/includes/functions/featured.php
/includes/functions/specials.php

Do you use SaleMaker?

Next, look at the file:
/admin/includes/modules/category_product_listing.php

Test turning off the Category Dropdown list and see if it helps with speed ...

Change the line around 47 from:

if ($_SESSION['display_categories_dropdown'] == 0) {

> 
> to read:
> ```
  if (false && $_SESSION['display_categories_dropdown'] == 0) {

To turn off the test for Linked Products which is a heavy load, go down to around line 165 and change:

  if (zen_get_products_to_categories($categories->fields['categories_id'], true, 'products_active') == 'true') {
    echo '  ' . zen_image(DIR_WS_IMAGES . 'icon_yellow_on.gif', IMAGE_ICON_LINKED);
  }

> 
> to read:
> ```
      if (false && zen_get_products_to_categories($categories->fields['categories_id'], true, 'products_active') == 'true') {
        echo '  ' . zen_image(DIR_WS_IMAGES . 'icon_yellow_on.gif', IMAGE_ICON_LINKED);
      }

I would first try the changes to the specials.php and featured.php ... then try the other changes ...

See if you need more than that for speed ...

NOTE: v1.3.9e is out and much faster on several things ...

If you do not upgrade, make sure you have all Security Patches and Updates:
http://www.zen-cart.com/forum/showthread.php?t=131115

and that you have added all Known Bug Fixes:
http://www.zen-cart.com/forum/showthread.php?t=82619

If you do use SaleMaker, then you will want to also check if the changes in:
/includes/functions/salemaker.php

help as well ...

There are a lot of checks that can be done on the database tables but they need to be checked and double checked before cleaning up the records ...

Common things to watch for are that you have matches for:
products
products_description

categories
categories_description

Checking things such as ...

all products_id in the products table are in the products_to_categories table ...

all categories_id in the products_to_categories table are in the categories table ...

all master_categories_id in the products table are in the categories table ...

There are a lot more but this is a start ...

I will do the Salemaker compare this morning, too... thank you!

Regarding the comparison... I was in the hopes that there would be a query that would left join and show nulls where there are holes for doublechecking the database.

Ajeh:

If you do use SaleMaker, then you will want to also check if the changes in:
/includes/functions/salemaker.php

help as well ...

There are a lot of checks that can be done on the database tables but they need to be checked and double checked before cleaning up the records ...

Common things to watch for are that you have matches for:
products
products_description

categories
categories_description

Checking things such as ...

all products_id in the products table are in the products_to_categories table ...

all categories_id in the products_to_categories table are in the categories table ...

all master_categories_id in the products table are in the categories table ...

There are a lot more but this is a start ...

I came up with the following queries to check what you said to start checking ... i hope the logic is right....

all master_categories_id in the products table are in the categories table ... returns 0 results (great if the query is right)

SELECT
`zen_products`.`master_categories_id`
FROM
`zen_products`
Inner Join `zen_categories` ON `zen_categories`.`categories_id` = `zen_products`.`master_categories_id`
where `zen_categories`.`categories_id` is null

all categories_id in the products_to_categories table are in the categories table ...returns 0 results (great if the query is right)

SELECT
`zen_categories`.`categories_id`,
`zen_products_to_categories`.`products_id`
FROM
`zen_categories`
Inner Join `zen_products_to_categories` ON `zen_products_to_categories`.`categories_id` = `zen_categories`.`categories_id`
where `zen_products_to_categories`.`products_id`
 is null

all products_id in the products table are in the products_to_categories table ... returns 61 results (spot checking the first two reveals that those products have a blank entry on the products table - i need to go through and handcheck the rest of the 61 returned)

SELECT
`zen_products_to_categories`.`products_id` as zptc_prodid,
`zen_products`.`products_id` as zp_prodid,
`zen_products_to_categories`.`categories_id` as zptc_catid
FROM
`zen_products_to_categories`
right Join `zen_products` ON `zen_products_to_categories`.`products_id` = `zen_products`.`products_id`
where `zen_products_to_categories`.`categories_id`is null

who knew cleaning could be so fun. :)

These might help too:
MATCH MISSING PRODUCTS_TO_CATEGORIES

SELECT * FROM products WHERE products.products_id NOT IN (SELECT products_to_categories.products_id FROM products_to_categories);

SELECT * FROM products_to_categories WHERE products_to_categories.products_id NOT IN (SELECT products.products_id FROM products);

MATCH MISSING ON master_categories_id

SELECT * FROM products WHERE products.master_categories_id NOT IN (SELECT products_to_categories.categories_id FROM products_to_categories);

SELECT * FROM products WHERE products.master_categories_id NOT IN (SELECT categories.categories_id FROM categories);

NOTE: these need to be edited for prefixes ...

ksoup ... how much did the changes speed up your Categories/Products?

Does it need more tweaking? :unsure:

I was just testing as you wrote... it is rockin' through now! Thank you!

Ajeh:

ksoup ... how much did the changes speed up your Categories/Products?

Does it need more tweaking? :unsure: