1

I want to optimize simple ajax call which retrieves product information. Just getting product information will take 13MB of memory:

require_once('app/Mage.php');
Mage::app('default');
$_product = Mage::getModel('catalog/product')->load(1); 
var_dump($_product->getData());

But if I make raw sql query it takes only 0.5MB:

  $file = $_SERVER['DOCUMENT_ROOT']."/app/etc/local.xml";
  $xml = simplexml_load_file($file);
  $username = (string)$xml->global[0]->resources->default_setup->connection->username;
  $password = (string)$xml->global[0]->resources->default_setup->connection->password;     
  $dbname = (string)$xml->global[0]->resources->default_setup->connection->dbname;
  $conn = new PDO("mysql:host=localhost;dbname=$dbname;charset=utf8",$username,$password);
  $sql = "SELECT * FROM (
      SELECT
          ce.sku,
          ea.attribute_id,
          ea.attribute_code,
          CASE ea.backend_type
             WHEN 'varchar' THEN ce_varchar.value
             WHEN 'int' THEN ce_int.value
             WHEN 'text' THEN ce_text.value
             WHEN 'decimal' THEN ce_decimal.value
             WHEN 'datetime' THEN ce_datetime.value
             ELSE ea.backend_type
          END AS value,
          ea.is_required AS required
      FROM catalog_product_entity AS ce
      LEFT JOIN eav_attribute AS ea
          ON ce.entity_type_id = ea.entity_type_id
      LEFT JOIN catalog_product_entity_varchar AS ce_varchar
          ON ce.entity_id = ce_varchar.entity_id
          AND ea.attribute_id = ce_varchar.attribute_id
          AND ea.backend_type = 'varchar'
      LEFT JOIN catalog_product_entity_int AS ce_int
          ON ce.entity_id = ce_int.entity_id
          AND ea.attribute_id = ce_int.attribute_id
          AND ea.backend_type = 'int'
      LEFT JOIN catalog_product_entity_text AS ce_text
          ON ce.entity_id = ce_text.entity_id
          AND ea.attribute_id = ce_text.attribute_id
          AND ea.backend_type = 'text'
      LEFT JOIN catalog_product_entity_decimal AS ce_decimal
          ON ce.entity_id = ce_decimal.entity_id
          AND ea.attribute_id = ce_decimal.attribute_id
          AND ea.backend_type = 'decimal'
      LEFT JOIN catalog_product_entity_datetime AS ce_datetime
          ON ce.entity_id = ce_datetime.entity_id
          AND ea.attribute_id = ce_datetime.attribute_id
          AND ea.backend_type = 'datetime'
      WHERE ce.sku = 'test'
    ) AS tab
    WHERE tab.value != '';
  ";
  foreach ($conn->query($sql) as $row) {
  var_dump($row);
  }
  var_dump(memory_get_usage()/1000/1000);

I believe I should use in such case raw query because the ajax call will be made for every page view and extra 13MB for every page view just for that is a lot. Anyway I am wondering if there is any other way which would spend less memory but still would use Magento model.

3
  • how about caching the result? this way you will only get 13MB of memory on the first call then it will take even less than 0.5MB (I assume).
    – Marius
    Aug 24, 2015 at 8:59
  • Unfortunately I can not use cache because the data depends also on user activity (how many pages user visited, referral url, user group, ...).
    – JohnyFree
    Aug 24, 2015 at 9:02
  • Magento pre-loads the models for you, so they'll obviously weigh way more than a simple SQL row. If you only need bits and pieces of information and feel comfortable with JOINing tons of tables, then yes, you're better off writing down your own queries. Aug 24, 2015 at 9:09

1 Answer 1

3

Loading configuration cache, initializing store models etc. produces a certain overhead, so you will never get to memory usage as low as your minimal PHP script.

As an example I created a script that does nothing but initialize Magento:

<?php
require 'app/Mage.php';
Mage::app();
echo number_format(memory_get_peak_usage());

Output: 6,922,712

And loading the product model involves more SQL queries, for example to add inventory data and load store specific attribute values. Of course the resulting objects need more memory than your var_dump($row);

So in certain cases, an optimized PHP only solution like yours is desirable1. However, you'll lose the comfort and safety of the framework and have to be extra careful about things like error handling and input validation.


1) For example, we offer an optimized PHP only version of the search suggest feature in our IntegerNet_Solr extension. This is something where caching the whole response is not feasible and every millisecond counts for better user experience.

1
  • Good answer.. @fschmengler.. and can you please put more details
    – Amit Bera
    Aug 24, 2015 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.