I am having an issue with load times in the Sales_Order_Grid
after join creating three joins to add additional columns.
This has significantly increased loading times and I was wondering if there is a way to increase the speed or optimise the code to prevent the sluggishness?
Here are my joins
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
/* This adds the additional Joins*/
$collection->getSelect()->joinLeft(array('sfop' => 'sales_flat_order_payment'),
'sfop.parent_id=main_table.entity_id', array(
'sfop.method', 'sfop.additional_information'));
$collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),
'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street',
'sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone'));
$collection->getSelect()->joinLeft(array(
'item'=>$collection->getTable('sales/order_item')),
'item.order_id=`main_table`.entity_id AND item.product_type="simple"',
array(
'skus' => new Zend_Db_Expr('group_concat(item.sku SEPARATOR ", ")'),
'name' => new Zend_Db_Expr('group_concat(item.name SEPARATOR ", ")'),
'brand' => new Zend_Db_Expr('group_concat(item.brand SEPARATOR ", ")')
)
);
/* End additional Joins*/
$collection->getSelect()->group('main_table.entity_id');
$this->setCollection($collection);
return parent::_prepareCollection();
}
I've added the columns for the information that I need to display without any errors
...
$this->addColumn('customer_firstname', array(
'header' => Mage::helper('sales')->__('Customer Firstname'),
'width' => '80px',
'index' => 'customer_firstname',
'type' => 'text',
'filter_index' => 'customer_firstname'
));
...
The following 0 – 19 (20 total, Query took 93.0730 sec)
SELECT `main_table`.*, `sfop`.`cc_type`, `sfop`.`cc_last4`, `sfop`.`cc_type`, `sfop`.`last_trans_id`, `sfop`.`method`, `sfop`.`additional_information`, `billing`.`postcode`, `sfoa`.`street`, `sfoa`.`city`, `sfoa`.`region`, `sfoa`.`postcode`, `sfoa`.`telephone`, group_concat(item.sku SEPARATOR ", ") AS `skus`, group_concat(item.name SEPARATOR ", ") AS `name`, group_concat(item.nego SEPARATOR ", ") AS `nego` FROM `sales_flat_order` AS `main_table`
LEFT JOIN `sales_flat_order_payment` AS `sfop` ON sfop.parent_id=main_table.entity_id
LEFT JOIN `sales_flat_order_address` AS `billing` ON main_table.entity_id = billing.parent_id AND billing.address_type="billing"
LEFT JOIN `sales_flat_order_address` AS `sfoa` ON main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"
INNER JOIN `sales_flat_order_item` AS `item` ON item.order_id=`main_table`.entity_id AND item.product_type="simple" GROUP BY `main_table`.`entity_id` ORDER BY main_table.created_at DESC LIMIT 20
The question is how do I optimise the Grid to increase speed?
Hope someone can help??
Best Answer
The speed optimization tip I can give you will also help you avoid potential issues:
Never ever ever ever perform joins from
sales_flat_order_grid
on live tables such assales_flat_order_address
; any 'lock' introduced from admins performing work (such as sorting by an unindexed column) will also lock the tables for orders being placed. This contributes to all sorts of issues that are difficult to diagnose and untangle like orders that clear the gateway but cannot be re-placed after a deadlock or lock wait timeout due to Paypal or Authorize (or other gateways) not accepting the reserved order id for a now second "payment".Instead, create the desired columns in
sales_flat_order_grid
and display the information from there. In certain cases the existence of the attribute name as a column name in the order placement will automagically add the desired values into the sales grid. In other cases you may need to use an observer.In summary, put the data you want to view in the admin into the admin grid itself.
Best of luck.