Sql-server – Simplifying query by adding Computed Column, performance impact

performancequery-performancesql-server-2000t-sql

The dev team came across the following query (might be incomplete; it's retyped from a screenshot) which had caused slowness:

SELECT d.catalogno, d.itemid,
  SUM(lineamount+taxamount) AS Amount,
  SUM(qty) AS Qty,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.basiccostprice
  END AS cog,
  d.salesprice,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.salesprice/d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.salesprice/d.basiccostprice
  END AS coeff
FROM custinvoicetrans t
WITH (nolock)
INNER JOIN
  pwlmy_dcatalog d
  WITH (nolock)
  ON t.itemid=d.itemid
INNER JOIN
  pwlmy_hcatalog h
  WITH (nolock)
  ON h.catalogno=d.catalogno
WHERE
  t.invoicedate>=h.effectivedatefrom
GROUP BY
  d.catalogno, d.itemid,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.basiccostprice
  END,
  d.salesprice,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.salesprice/d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.salesprice/d.basiccostprice
  END

I'm wondering… if I create a view like such:

CREATE VIEW pwlmy_dcatalog_cog AS
  SELECT *,
    CASE
      WHEN costprice>=basiccostprice THEN costprice
      WHEN costprice<basiccostprice THEN basiccostprice
    END AS cog
  FROM pwlmy_dcatalog

I should be able to simplify the query to the following:

SELECT d.catalogno, d.itemid,
  SUM(lineamount+taxamount) AS Amount,
  SUM(qty) AS Qty,
  d.cog,
  d.salesprice,
  d.salesprice/d.cog AS coeff,
FROM custinvoicetrans t
WITH (nolock)
INNER JOIN
  pwlmy_dcatalog_cog d
  WITH (nolock)
  ON t.itemid=d.itemid
INNER JOIN
  pwlmy_hcatalog h
  WITH (nolock)
  ON h.catalogno=d.catalogno
WHERE
  t.invoicedate>=h.effectivedatefrom
GROUP BY
  d.catalogno, d.itemid,
  d.cog,
  d.salesprice,
  d.salesprice/d.cog

Do you think the simplified query will have a significant impact to overall performance?

Note: I don't dare add a computed column into the pwlmy_dcatalog table; codes relying on that table might break spectacularly.

Best Answer

Moving the calculation to the view won't change performance - it's still done at runtime. So all you've changed is the complexity of the outer query.

In order to change performance you would need to add the computed column to the table, and persist it or index it. This means the work of the calculation is done at insert/update time instead of query time. (Persisting only works in 2005+.)

Now, other factors are at play too. For example, do you have any evidence that this calculation actually affects performance? Maybe the query performs the same without it? Also using the view may cause other performance issues, for example if it involves additional columns, calculations or tables that this query doesn't actually need.

Related Question