Performance..

[gemohler]

Lets say I have 250,000 products.

And right now they are all in one Category (just testing)...

And it can be 'slow' to click on a single product.

Would be 'faster' if I broke up the products into multiple root categories.

There will be no categories for customers to sort through, it would be pointless in this use case, NO way to ever truly organize it..you know you need a thing, you enter the PN, and you get it.

Just wondering how that might speed things up.


Also, did the 'Parse Time Display' option go away? I used to see it in 'Logging' options.

TY

[gemohler]

Question #2:

This is identified as a 'slow query' on my machine"

'SELECT ip_address from admin_activity_log LIMIT N'

its a very long table..ans as I import products it gets larger...faaaaast.

[mc12345678]

Question #2:

This is identified as a 'slow query' on my machine"

'SELECT ip_address from admin_activity_log LIMIT N'

its a very long table..ans as I import products it gets larger...faaaaast.

What is the value of N in your case?

Does the show query issue for that query go away if you change:

SELECT ip_address from

To:

SELECT log_id from

?
This query is located in: admin/includes/classes/class.admin.zcObserverLogWriterDatabase.php at line 56. Hmm, in version 1.5.8, but you don't declare what version you are or were using...

[gemohler]

What is the value of N in your case?

Does the show query issue for that query go away if you change:

SELECT ip_address from

To:

SELECT log_id from

?
This query is located in: admin/includes/classes/class.admin.zcObserverLogWriterDatabase.php at line 56. Hmm, in version 1.5.8, but you don't declare what version you are or were using...

Apologies, latest current code.

The N value is always 1..just as I add products a lot of entries really fast fall in here...and I'm working to optimize what I can in the DB without breaking required tooling.

[mc12345678]

Lets say I have 250,000 products.

And right now they are all in one Category (just testing)...

And it can be 'slow' to click on a single product.

Would be 'faster' if I broke up the products into multiple root categories.

There will be no categories for customers to sort through, it would be pointless in this use case, NO way to ever truly organize it..you know you need a thing, you enter the PN, and you get it.

Just wondering how that might speed things up.


Also, did the 'Parse Time Display' option go away? I used to see it in 'Logging' options.

TY

It's been a while since I've discussed this. My reflection is that there is speed improvement by use of categories to hold product. Forever it should be balanced in some way. For example, if you have the 25,000 product all in one category, it is expected that loading that category may be slow and depending on how the site is setup, it could be that you could still go to a home page on the site or you could be presented with the category of the product. But then if you put each product into its own single category AND categories are shown in every page load, then this will be really slow on each page load and the list would be long. Some optimization has been found where a category listing doesn't try to access the product within the category until specifically requested.

Point I'm trying to make based off of negotiating of previous code review is that should have a mix of categories with a "reasonable" quantity of product in the desired leaf category.

[gemohler]

It's been a while since I've discussed this. My reflection is that there is speed improvement by use of categories to hold product. Forever it should be balanced in some way. For example, if you have the 25,000 product all in one category, it is expected that loading that category may be slow and depending on how the site is setup, it could be that you could still go to a home page on the site or you could be presented with the category of the product. But then if you put each product into its own single category AND categories are shown in every page load, then this will be really slow on each page load and the list would be long. Some optimization has been found where a category listing doesn't try to access the product within the category until specifically requested.

Point I'm trying to make based off of negotiating of previous code review is that should have a mix of categories with a "reasonable" quantity of product in the desired leaf category.

Ya..I think what I will do is create root categories based on part number bases.

1xx will be Category "100"
And so on, including A-Z. Easy enough to do.

[mc12345678]

Apologies, latest current code.

The N value is always 1..just as I add products a lot of entries really fast fall in here...and I'm working to optimize what I can in the DB without breaking required tooling.

As identified in the file at the provided location, the query is "simply" trying to determine the existence of any record in the table. The particular field returned is a key of the table but is not the primary key. My suggestion was to change the query to use the primary key.

The idea is that even when the table is large as soon as a record is found it should stop looking. Further if there are no records in the table, then it should be that much faster (one less record to evaluate).

Now if you have gone and done some form of database optimization that has affected table characteristics, that too could explain the issue.

To be honest right now I would be trying to evaluate if the existing method is considered the most efficient/fastest way to accomplish the described task.

If you find a faster method, you can always contribute at https://github.com/zencart/zencart. The code maintainers are always looking for ways to micro optimize operations.

[gemohler]

As identified in the file at the provided location, the query is "simply" trying to determine the existence of any record in the table. The particular field returned is a key of the table but is not the primary key. My suggestion was to change the query to use the primary key. The idea is that even when the table is large as soon as a record is found it should stop looking. Further if there are no records in the table, then it should be that much faster (one less record to evaluate). Now if you have gone and done some firm of database optimization that has affected table characteristics, that to could explain the issue.

To be honest right now I would be trying to evaluate if the existing method is considered the most efficient/fastest way to accomplish the described task.

If you find a faster method, you can always contribute at https://github.com/zencart/zencart. The code maintainers are always looking for ways to micro optimize operations.

I have not changed any DB characteristics, just a few missing index here and there that have helped..or seemed to have.

[gemohler]

As identified in the file at the provided location, the query is "simply" trying to determine the existence of any record in the table. The particular field returned is a key of the table but is not the primary key. My suggestion was to change the query to use the primary key.

The idea is that even when the table is large as soon as a record is found it should stop looking. Further if there are no records in the table, then it should be that much faster (one less record to evaluate).

Now if you have gone and done some form of database optimization that has affected table characteristics, that too could explain the issue.

To be honest right now I would be trying to evaluate if the existing method is considered the most efficient/fastest way to accomplish the described task.

If you find a faster method, you can always contribute at https://github.com/zencart/zencart. The code maintainers are always looking for ways to micro optimize operations.

I dont write code..but if I did..

I would set a variable that says "Logs are not initialized" at the top
Then in the section at line 56, use isset to see if the variable exists. If not, continue with the if test to make sure it's set or not (The if <1 result test)

Then at line 108, initialize the logs, then set the variable to now say that logs have been initialized.

This should remove this burn of an if statement that's spamming the DB with:
SELECT ip_address from " . TABLE_ADMIN_ACTIVITY_LOG . " LIMIT 1"

Requests.

Im logging about 2 seconds per minute of time spent here, while doing imports...100k a minute maybe??


AND I just found the cache on my HP raid controller died today, dead supercap. Grr