I am currently setting up our website with your B2BKing plugin active. Everything seems to work as it should. However, we have a number of customers who have different pricing for all of our 4000 items than most customers. I have auto-created dynamic rules per itemno/custno/minqty combination, which resulted in around 370.000 separate dynamic rules. They all work as intended.
The only problem I have is performance. When I'm loading the shop page with the "Dynamic Rules - Fixed prices" active, it takes around 12 seconds to load and display 16 items, with the Dynamic Rules disabled it only takes around 2 to 3 seconds to load. It is clear to me that the dynamic rules for fixed prices are causing a major slowdown.
It sounds like a difference that won't be solved by just switching to a(n even) more powerfull hosting alone. Is this a known issue and do you have suggestions on how to speed things up?
In the wp_options table, the option b2bking_have_fixed_price_rules_list_ids contains, in my case, almost 13.000 items. These are then exploded into an array, and it takes quite a while to loop through all of them in the subsequent foreach loop.
Can you help me, what is this option supposed to be? As far as I can tell it contains post ids to dynamic rules, but since there are only about 13.000 id's in this option value it's looks far from complete to me. So either this option is for something else I don't yet understand or the code is not taking into account all of my dynamic rules, but only 13.000 of them.
For now I'm assuming that b2bking_have_fixed_price_rules_list_ids should contain all dynamic rules ids . In my case, it should contain about 370.000 id's, separated by comma.
With this snippet I'm able to speed things up tremendously because it only queries the b2bking_rule posts of type "fixed_price" that apply to the product at hand. I could have also put in an extra meta_query on user, but I also have conditions that apply to everyone so I left that out. The snippets queries only the rules that can apply and is left with 10 relevant ids instead of 13.000 .
This is by no means a complete fix because it does not take into account categories and cart totals (both of which are not applicable to my setup). However, it does goes to show that some optimization really is in order here to further improve this wonderful plugin. Looping through 13.000 (or even 370.000 records if the wp_options would have contained all posts) when only 10 rules have any chance of ever being applicable is all but efficient. It would be better if the plugin first queries the rules that may apply on a superficial level, and then only loop those to check the conditions to see which ones actually apply.
I've now had a chance to look through everything. Thank you for the detailed explanation.
Generally we had not expected users to create such large numbers of rules - in this situation indeed, we must do further optimization.
When the plugin was first created, the query for rules was actually much more specific: it was similar to yours, it included the exact product, the exact user, etc. Surprisingly to us, having a large number of meta query conditions actually created a lot of performance problems, because the SQL statements created by WP were very inefficient with a lot of OR conditions (for large wp_postmeta tables, they were very slow). So believe it or not, querying all, and running through all with a FOREACH is faster in most cases than the alternative of only querying the very specific rules needed.
I agree more work must be done for situations such as yours with large numbers of rules. Thank you for sharing the code with us.
Hi,
I am currently setting up our website with your B2BKing plugin active. Everything seems to work as it should. However, we have a number of customers who have different pricing for all of our 4000 items than most customers. I have auto-created dynamic rules per itemno/custno/minqty combination, which resulted in around 370.000 separate dynamic rules. They all work as intended.
The only problem I have is performance. When I'm loading the shop page with the "Dynamic Rules - Fixed prices" active, it takes around 12 seconds to load and display 16 items, with the Dynamic Rules disabled it only takes around 2 to 3 seconds to load. It is clear to me that the dynamic rules for fixed prices are causing a major slowdown.
It sounds like a difference that won't be solved by just switching to a(n even) more powerfull hosting alone. Is this a known issue and do you have suggestions on how to speed things up?
Kindest regards,
Maarten
After some more debugging i was able to pinpoint a big slowdown on
$fixed_price_rules_option = get_option('b2bking_have_fixed_price_rules_list_ids', '');
In the wp_options table, the option b2bking_have_fixed_price_rules_list_ids contains, in my case, almost 13.000 items. These are then exploded into an array, and it takes quite a while to loop through all of them in the subsequent foreach loop.
Can you help me, what is this option supposed to be? As far as I can tell it contains post ids to dynamic rules, but since there are only about 13.000 id's in this option value it's looks far from complete to me. So either this option is for something else I don't yet understand or the code is not taking into account all of my dynamic rules, but only 13.000 of them.
For now I'm assuming that b2bking_have_fixed_price_rules_list_ids should contain all dynamic rules ids . In my case, it should contain about 370.000 id's, separated by comma.
With this snippet I'm able to speed things up tremendously because it only queries the b2bking_rule posts of type "fixed_price" that apply to the product at hand. I could have also put in an extra meta_query on user, but I also have conditions that apply to everyone so I left that out. The snippets queries only the rules that can apply and is left with 10 relevant ids instead of 13.000 .
This is by no means a complete fix because it does not take into account categories and cart totals (both of which are not applicable to my setup). However, it does goes to show that some optimization really is in order here to further improve this wonderful plugin. Looping through 13.000 (or even 370.000 records if the wp_options would have contained all posts) when only 10 rules have any chance of ever being applicable is all but efficient. It would be better if the plugin first queries the rules that may apply on a superficial level, and then only loop those to check the conditions to see which ones actually apply.
//** custom code!! **
$nect_arr2 = array();
$nect_args = array(
'post_type' => 'b2bking_rule',
'fields' => 'ids',
'meta_query' => array(
array(
'key' => 'b2bking_rule_what',
'value' => 'fixed_price',
),
array(
'key' => 'b2bking_rule_applies',
'value' => 'product_' . $current_product_id,
),
),
);
$nect_query = new WP_Query( $nect_args );
$nect_rules = $nect_query->posts;
$nect_arr2 = $nect_rules;
if (count($nect_arr2) > 0) {
$fixed_price_rules_v2_ids = $nect_arr2;
} else {
$fixed_price_rules_v2_ids = array();
}
//** end of custom code**
Hi Maarten,
I've now had a chance to look through everything. Thank you for the detailed explanation.
Generally we had not expected users to create such large numbers of rules - in this situation indeed, we must do further optimization.
When the plugin was first created, the query for rules was actually much more specific: it was similar to yours, it included the exact product, the exact user, etc. Surprisingly to us, having a large number of meta query conditions actually created a lot of performance problems, because the SQL statements created by WP were very inefficient with a lot of OR conditions (for large wp_postmeta tables, they were very slow). So believe it or not, querying all, and running through all with a FOREACH is faster in most cases than the alternative of only querying the very specific rules needed.
I agree more work must be done for situations such as yours with large numbers of rules. Thank you for sharing the code with us.