Tag: sql

Always, ALWAYS use sub-selects!

SQL with sub-sub-sub-selects: 9 minutes

SELECT p.sku AS "Product code",
 p.annotation AS "Product Description",
  (SELECT COUNT(orders_products.fk_product_id) AS "count"
   FROM orders_products
   INNER JOIN orders 
     ON orders.id = orders_products.fk_order_id 
     AND orders.order_created >= '2008-12-01 00:00:00' 
     AND orders.order_created < = '2008-12-07 24:00:00'
   INNER JOIN products 
     ON products.id = orders_products.fk_product_id 
     AND products.sku = p.sku
   WHERE orders_products.fk_order_status_id >= 30) AS "Total number of units sold",
    (
      SELECT SUM((orders_products.price-orders_products.discount)/1.15) AS "sum"
      FROM orders_products
      INNER JOIN orders 
        ON orders.id = orders_products.fk_order_id 
        AND orders.order_created >= '2008-12-01 00:00:00'
        AND orders.order_created < = '2008-12-07 24:00:00'
      INNER JOIN products 
        ON products.id = orders_products.fk_product_id 
        AND products.sku = p.sku
      WHERE orders_products.fk_order_status_id >= 30
     ) AS "Total value"
     FROM products p
      WHERE (
        SELECT COUNT(orders_products.fk_product_id) AS "count"
        FROM orders_products
        INNER JOIN orders 
          ON orders.id = orders_products.fk_order_id 
          AND orders.order_created >= '2008-12-01 00:00:00'
          AND orders.order_created < = '2008-12-07 24:00:00'
        INNER JOIN products 
          ON products.id = orders_products.fk_product_id
          AND products.sku = p.sku
        WHERE orders_products.fk_order_status_id >= 30
      ) > 0

Inner joins for the same thing: 400ms

SELECT products.sku, products.annotation,
  COUNT(orders_products.fk_product_id) AS "count",
  SUM ((orders_products.price-orders_products.discount)/1.15) AS "sum"
  FROM orders_products
  INNER JOIN orders 
    ON orders.id = orders_products.fk_order_id 
    AND orders.order_created >= '2008-12-01 00:00:00' 
    AND orders.order_created < = '2008-12-07 24:00:00'
  INNER JOIN products 
    ON products.id = orders_products.fk_product_id
  WHERE orders_products.fk_order_status_id >= 30
  GROUP BY products.sku, products.annotation

transactions

function run() {
  ...
  //DONT TOUCH !!! -&gt; THIS DB TRANS STARTS, BECAUSE XYZ MODUL CONSIST OF DB::Commit() clausule
  DB::Trans();
  return $returnVal;
}

Hey guys, let’s start a transaction here and expect that no one will ever change the code in any of the other 2000 files. And if they do, I’m sure that while they’ll be editing “modul” XYZ, I’m sure they will know that transaction starts here.

OCD code?

if (!empty(fetchResult()) and isObject(fetchResult()) and is_a(fetchResult(), "objectType")) {
  $result = fetchResult();
  doSomethingElse();
}
elseif (isObject(fetchResult()) {
  $result = fetchResult();
}

I think the guy who wrote it was slightly OCD. And he definitely forgot that fetchResult() fetches results… from the database.

After optimizing aforementioned code and singleton-ifying the fetchResult(), number of queries made in one pageload went down to 300 from 800. Our code is just awesome.