Performance issues
Hey guys,
I have been working on a new Commerce webshop for a client of ours, but now that I'm closing on the final issues, performance seems to be going down the drain.
I'm using a Solr-based search api index, for which I have a catalog view, using fields. (title, image-field, add to cart, dimensions-field)
To add the add to cart form, I need a relationship to the commerce_product entity, but this causes a massive amount of queries to be executed...
Currently I'm displaying the view with 60 / 120 / 240 products, and this already amounts up to 4000+ DB queries. I tracked down some of these queries and noticed that the product entities are being loaded one-by-one, causing the node_view hooks to multiply the number of queries per product entity.
When I remove the commerce_product relationship, the number of queries stabilizes to around 400+ queries. It's still a lot, but a whole lot less than 4000.
After some more debugging, I found the culprit to be in the entity api module, method "pre_render":
File: entity/views/plugins/entity_views_plugin_row_entity_view.inc
From line 70:
public function pre_render($values) {
if (!empty($values)) {
list($this->entity_type, $this->entities) = $this->view->query->get_result_entities($values, !empty($this->relationship) ? $this->relationship : NULL, isset($this->field_alias) ? $this->field_alias : NULL);
}
// Render the entities.
if ($this->entities) {
$render = entity_view($this->entity_type, $this->entities, $this->options['view_mode']);
// Remove the first level of the render array.
$this->rendered_content = reset($render);
}
}The line of code to follow is:
$this->view->query->get_result_entities($values, !empty($this->relationship) ? $this->relationship : NULL, isset($this->field_alias) ? $this->field_alias : NULL);Looking further, I came about the function "get_result_entities" in the search api views query class:
File: search_api/contrib/search_api_views/includes/query.inc
From line 353:
public function get_result_entities($results, $relationship = NULL, $field = NULL) {
list($type, $wrappers) = $this->get_result_wrappers($results, $relationship, $field);
$return = array();
foreach ($wrappers as $id => $wrapper) {
try {
$return[$id] = $wrapper->value();
}
catch (EntityMetadataWrapperException $e) {
// Ignore.
}
}
return array($type, $return);
}Here the entity object is loaded one by one, creating a whole lot of DB queries.
Is there any way of getting around this problem? I can enable caching for anon. users, but 90% of my visitors auth themselves for special pricing etc, so caching isn't an option for those visitors.
Comments
Hey Ryan, thx for your awesome suggestion to preload all commerce products, so the view can profit from static cache.
The general loading of the catalog view already uses less queries than before with this method (with a views pre-execute hook :-)), but we notice that the add to cart form also generates a lot of overhead.
By xhproffing our code, we also found out that 60% of the response times were spent in the theme layer, generating the various elements for a minimum of 60 products per page.
The story continues!
Awesome, glad to hear that worked out! : )
As for the Ad to Cart form and element generation, wow. I guess it's a result of injected product fields? It's not attempting to render fields that aren't ultimately shown on the page, is it?
We are now trying to find out why the add to cart + line item generation per product is taking up 60% of the page loading times...
If we find anything, I'll let you know.
Hey Ryan,
We launched the website about a month ago, and everything is working fine, even for auth'ed users!
We finally decided to replace the default add-to-cart forms by a simpler HTML add-to-cart form solution, which just passes on product ID and quantity to a hook_init() handler which then implements the same logic as the default add-to-cart form.
It creates an empty line item, fills in the properties from our POST and saves the line item.
This eventually reduced the theme rendering times by another 80%, coming down to an average page loading time of 1-2 seconds for authed users on a 120 products grid view.
It has drawbacks for sure but as performance is an equally important experience for customers, we opted this as our best scenario.
By now the customer has already accepted dozens of orders in his first month of Drupal Commerce and is totally contented with the result!
For those who are interested to see the result: www.denolf.com , one of belgium's leading plant nurseries!