5
\$\begingroup\$

The end result I am trying to achieve is an array containing the total ordered qty for each product. I have been successful but the query takes about 20 minutes to run and I'm wondering if there is anyone who may know of some way of improving this? The details and current query are as follows:

  • All columns involved in a join or where clause are indexed.

  • Tables have been optimized.

orders_table:

  • 2 million rows

  • duplicate product codes where more than one order has been made for the same product (these can be grouped by product_code and pack_size_ordering_unit)

  • has product code and barcode

product_table:

  • 300 thousand rows
  • no duplicates
  • has product code and barcode
  • query should be run for products where the distribution route is not 'C'
  • query can be run for any products within any specified categories

product_pack_sizes: - 2 million rows - each product can be ordered in various pack sizes (e.g. 1 store pack of 4 units, 2 pallets of 84 units ect.) - no duplicates only has barcode

SELECT product_table.ProductCode as product_code, SUM(grouped_store_orders_table.po_qty*product_pack_sizes.pack_size_qty) AS order_qty
FROM (SELECT orders_table.barcode, orders_table.po_qty, orders_table.po_ordering_unit
FROM orders_table 
GROUP BY orders_table.barcode, orders_table.po_ordering_unit) AS grouped_store_orders_table
LEFT JOIN product_table
ON product_table.barcode = grouped_store_orders_table.barcode
LEFT JOIN product_pack_sizes
ON grouped_store_orders_table.barcode = product_pack_sizes.barcode AND grouped_store_orders_table.po_ordering_unit = product_pack_sizes.unit_of_measure
WHERE product_table.Category IN (2,3,4,5) AND product_table.DistroRoute <> 'C'
GROUP BY product_table.ProductCode

Execution plan:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    36756   Using temporary; Using filesort
1   PRIMARY product_table   ref barcode,Category    barcode 4   grouped_store_orders_table.article  1   Using where
1   PRIMARY product_pack_sizes  ref article,unit_of_measure article 4   trends.product_table.barcode    3   
2   DERIVED orders_table    ALL NULL    NULL    NULL    NULL    2137680 Using temporary; Using filesort
\$\endgroup\$
3
\$\begingroup\$

First things first - if you're going to need to maintain SQL, it's best to make it easy to maintain first: start with proper indentation and vertical spacing:

SELECT 
     product_table.ProductCode as product_code
    ,SUM(grouped_store_orders_table.po_qty*product_pack_sizes.pack_size_qty) AS order_qty
FROM 
    (SELECT 
         orders_table.barcode
        ,orders_table.po_qty
        ,orders_table.po_ordering_unit
     FROM orders_table 
     GROUP BY 
         orders_table.barcode, 
         orders_table.po_ordering_unit) AS grouped_store_orders_table
LEFT JOIN product_table
    ON product_table.barcode = grouped_store_orders_table.barcode
LEFT JOIN product_pack_sizes
    ON grouped_store_orders_table.barcode = product_pack_sizes.barcode 
    AND grouped_store_orders_table.po_ordering_unit = product_pack_sizes.unit_of_measure
WHERE product_table.Category IN (2,3,4,5) 
    AND product_table.DistroRoute <> 'C'
GROUP BY 
    product_table.ProductCode;

Aliases should make your life easier - I like dropping the AS keyword since it's not really needed, and I like to keep my aliases short/concise - and don't use them where they're not needed.

You're performing these joins, and then filtering product categories with a where clause; I don't know your data, but you may be able to significantly reduce the amount of rows involved in the join by filtering products first:

LEFT JOIN (SELECT * FROM product_table 
           WHERE Category IN (2,3,4,5) AND DistroRoute <> 'C') products
    ON products.barcode = source.barcode
\$\endgroup\$
  • \$\begingroup\$ Thanks, great answer. This will also help by pre-filtering out any products that have a DistroRoute of 'C' which should save time when joining. I'll wait to see if there can be any further improvements but I think you may have cracked it. \$\endgroup\$ – Jamie Deakin Dec 17 '14 at 16:36
  • 1
    \$\begingroup\$ One issue I've just noticed. The WHERE clause was filtering products out that where not in the selected category or of the right DistroRoute but with this change they will be included if they are in the original orders_table data. If I change the join in your answer to a RIGHT JOIN it should solve this. \$\endgroup\$ – Jamie Deakin Dec 17 '14 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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