AP 30 - Avoid or delay ORDER BY in inline views

Severity

Caution

Type

Performance

Problem

As a rule of thumb you should leave ordering until the very end. Frequently it is not needed at all. Sorting data is generally an expensive operation in databases. It should be reserved for when you really need it.

WITH customer AS(
    SELECT sc.customerid 
          ,sc.customername 
          ,sb.buyinggroupname 
          ,ad.deliverymethodname 
      FROM SALES_CUSTOMERS sc 
      LEFT 
      JOIN SALES_BUYINGGROUPS sb 
        ON sc.buyinggroupid=sb.buyinggroupid
      LEFT 
      JOIN APPLICATION_DELIVERYMETHODS ad 
        on sc.DELIVERYMETHODID=ad.DELIVERYMETHODID
     ORDER BY sc.customerid)
 ,sales_order AS(
    SELECT sord.orderid 
          ,sord.customerid 
          ,sordl.quantity 
          ,sordl.unitprice 
          ,sordl.taxrate 
          ,sordl.stockitemid 
          ,sordl.orderlineid 
      FROM SALES_ORDERS sord 
      JOIN SALES_ORDERLINES sordl 
        ON sord.orderId=sordl.orderid)
 SELECT customer.buyinggroupname 
      ,SUM(sales_order.quantity*sales_order.unitprice) total_sales     
  FROM customer 
  LEFT 
  JOIN sales_order 
    ON customer.customerid=sales_order.customerid
 GROUP BY customer.buyinggroupname

There is no point in ordering the CTE customer by customerid. It does not

Solution

Avoid ORDER By in subqueries, common table expressions and inline views

If you know that your data will be used by a business intelligence tool or another client then you should leave the ordering to the client tool

Remove the ORDER BY clause

WITH customer AS(
    SELECT sc.customerid 
          ,sc.customername 
          ,sb.buyinggroupname 
          ,ad.deliverymethodname 
      FROM SALES_CUSTOMERS sc 
      LEFT 
      JOIN SALES_BUYINGGROUPS sb 
        ON sc.buyinggroupid=sb.buyinggroupid
      LEFT 
      JOIN APPLICATION_DELIVERYMETHODS ad 
        on sc.DELIVERYMETHODID=ad.DELIVERYMETHODID)
 ,sales_order AS(
    SELECT sord.orderid 
          ,sord.customerid 
          ,sordl.quantity 
          ,sordl.unitprice 
          ,sordl.taxrate 
          ,sordl.stockitemid 
          ,sordl.orderlineid 
      FROM SALES_ORDERS sord 
      JOIN SALES_ORDERLINES sordl 
        ON sord.orderId=sordl.orderid)
 SELECT customer.buyinggroupname 
      ,SUM(sales_order.quantity*sales_order.unitprice) total_sales     
  FROM customer 
  LEFT 
  JOIN sales_order 
    ON customer.customerid=sales_order.customerid
 GROUP BY customer.buyinggroupname

Legitimate use of anti pattern

Window functions require ORDER BY

Some databases have hacks to filter the top n number of records by using pseudo columns, e.g. Oracle

The following query will select the top 1 records from sales table ordered by sales amount. While this query works in principle it is also best avoided and replaced with a window function

SELECT * FROM (SELECT * FROM sales ORDER BY sales_amt DESC) WHERE rownum <= 1

SELECT * FROM (SELECT * FROM sales ORDER BY sales_amt DESC) WHERE rownum <= 1