Raw sql query vs Mage::getModel('catalog/product') vs?

Question

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.

Answer 1

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 desirable¹. 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.}