script causing cpu max limit - get_audiences_list()

[gilby]

Having just had this problem myself
I have found out a couple of relavent things

It appears that that the check for NULL on a field used in the joins is a lot faster.
Also a few versions of mysql had problems testing for NULL on fields not used in the joins.

So a first improvement is to replace the o.date_purchased field with o.customers_id field

SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN  TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.customers_id IS NULL

An altenative that you can try that seems to produce around the same times is

SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c 
WHERE c.customers_id NOT IN 
(
SELECT o.customers_id AS customers_id
FROM TABLE_ORDERS o
)

Both of these improved my selection times considerably

Obviously backup before trying either of these to see if they work for you

[DrByte]

Or just add a distinct index on orders.customers_id to improve performance considerably. (v1.3.9 does this during the upgrade):

ALTER TABLE orders ADD INDEX customers_id (customers_id);

Also, just a comment ... using the second approach you mentioned, the sub-select, could essentially return all records twice, thus being somewhat less efficient than the join approach.

[gilby]

Or just add a distinct index on orders.customers_id to improve performance considerably. (v1.3.9 will do this during the upgrade)


Also, just a comment ... using the second approach you mentioned, the sub-select, could essentially return all records twice, thus being somewhat less efficient than the join approach.

Thanks Doc
And of course the addition of the index made the selection of the original or my 2 alternatives irrelevant.
The speed increase from the use of the index is remarkable

[DrByte]

Thanks for confirming that. ;)

[capnhairdo]

The query in question works fine on a small dataset, but in the case of large amounts of order and/or customer data the query is not adequately optimized. So, to keep it will require fine-tuning it... It's really not high on my list of priorities of things to "fix".

I'm a little bit horrified that ZenCart is including SQL queries that are expected to fail when used under real world conditions and need to be tweaked by end-users to work correctly.

We have a 1.38a installation with about 16K customers and 50K customers. (Not especially high numbers for an ecommerce site.) As of today, our entire site was locking up and timing out, and I spent half the day tracking it down to this SQL query. According to the logs, this query was taking of 2,000 seconds to run. That's a HALF HOUR.

gilby's suggestion to replace the date_purchased field with customers_id field is a good one. Seems to work just as fast on orders_id, probably because both those fields are defined as non-NULL.

DrByte's suggestion to add an index on customers_id helped even more (though not necessary if you use orders_id instead of customers_id). Using an indexed column got the query running in .02 seconds. Quite a difference from 2000 seconds.

Hope this fix did become a priority and was implemented in 1.3.9.

[gilby]

It was :)

And.... You also should upgrade as well

You will find this and many other "fixes" are included as well to improve performance.

[DivaVocals]

Hope this fix did become a priority and was implemented in 1.3.9.

Not to speak for the good doctor, but I do believe you misunderstood what he meant when he stated "It's really not high on my list of priorities of things to "fix" "..

It's likely he meant that it was not a priority to fix thin in v1.3.8a since v1.3.9 was the thrust of the Zen developers focus and the issue would be addressed in THAT release... DrByte can of course confirm my interpretation.. this is how I took his words..

Given that DrBtye also stated that this was addressed in Zen Cart 1.3.9:

Or just add a distinct index on orders.customers_id to improve performance considerably. (v1.3.9 does this during the upgrade):

and as gilby posted it WAS addressed in v1.3.9, it seems that the thing to do to correct your situation is to upgrade your 1.3.8a shop to the latest version of Zen Cart (v1.3.9h as of this post) or apply the changes posted (as it appears you have done)

[juneloweelyn]

May i know how do i UNDO this code ?


update query_builder set query_category = '' where query_name = 'Customers who have never completed a purchase';


I wish to restore back to what it was before i install the SQL patch above.

Thank you!

[DrByte]

change set query_category = '' to set query_category = 'newsletter'