Avoiding multiple repetitive sub queries that are being used to derive columns in the select

Question

I have a view that consists of multiple sub queries that are used to derive columns in the select list (for keeping it simple I didn't specify all the sub queries). My question here is that is it totally okay to write such a query with so many sub queries in it or is there a better way to re-write it to avoid them...any best practices that can be followed. I tried looking at the option of doing a derived query or a cte but for some reason I am not able to get this piece together. I want to eliminate those repetitive sub queries if possible.

  SELECT a.id,
   (
     SELECT TOP 1
      name
     FROM x.dbo.Info l
     WHERE orderno = a.orderno
       AND releaseno = a.releaseno
       AND stamp =
       (
       SELECT MIN(stamp)
       FROM x.dbo.Info
       WHERE orderno = l.orderno
         AND releaseno = l.releaseno
         AND status = 'Released'
       )
     ORDER BY stamp DESC
   ) [shop_name],
   c.line_no,
   a.status,
   d.family,
   (
     SELECT TOP 1
      name
     FROM x.dbo.Info
     WHERE orderno = a.orderno
       AND releaseno = a.releaseno
       AND status NOT LIKE 'backflus%'
       AND status NOT LIKE 'so%'
     ORDER BY stamp DESC
   ) AS [lastworkplace],
   (
     SELECT TOP 1
      lstatus
     FROM x.dbo.Info
     WHERE orderno = a.orderno
       AND releaseno = a.releaseno
       AND status NOT LIKE 'backflus%'
       AND status NOT LIKE 'so%'
     ORDER BY stamp DESC
   ) AS [laststatus]
FROM BI.dbo.tblz a -- this is a view (not sure if that matters)
  LEFT JOIN X.dbo.tblx b
    ON b.id = a.salesorder
  LEFT JOIN X.dbo.tbls c
    ON c.tranid = a.salesorder
     AND c.itemid = a.assemblyid
     AND c.serialnum = a.ordercode
  LEFT JOIN Z.dbo.tbli d
    ON d.prodline = LEFT(COALESCE(STUFF(a.assemblyid, CHARINDEX('+', a.assemblyid), 1, ''), a.assemblyid), 2)
WHERE a.id = 'p'
  AND
  (
    LEFT(a.prun, 8) >= '20120101'
    OR a.prun IS NULL
  )
UNION ALL
SELECT a.id,
   (
     SELECT TOP 1
      name
     FROM x.dbo.Info l
     WHERE orderno = a.orderno
       AND releaseno = a.releaseno
       AND stamp =
       (
       SELECT MIN(stamp)
       FROM x.dbo.Info
       WHERE orderno = l.orderno
         AND releaseno = l.releaseno
         AND status = 'Released'
       )
     ORDER BY stamp DESC
   ) [shop_name],
   c.line_no,
   a.status,
   d.family,
   (
     SELECT TOP 1
      name
     FROM x.dbo.Info
     WHERE orderno = a.orderno
       AND releaseno = a.releaseno
       AND status NOT LIKE 'backflus%'
       AND status NOT LIKE 'so%'
     ORDER BY stamp DESC
   ) AS [lastworkplace],
   (
     SELECT TOP 1
      lstatus
     FROM x.dbo.Info
     WHERE orderno = a.orderno
       AND releaseno = a.releaseno
       AND status NOT LIKE 'backflus%'
       AND status NOT LIKE 'so%'
     ORDER BY stamp DESC
   ) AS [laststatus]
FROM BI.dbo.tblz a -- this is a view (not sure if that matters)
  LEFT JOIN X.dbo.tblx b
    ON b.id = a.salesorder
  LEFT JOIN X.dbo.tbls c
    ON c.tranid = a.salesorder
     AND c.itemid = a.assemblyid
     AND c.serialnum = a.ordercode
  LEFT JOIN Z.dbo.tbli d
    ON d.prodline = LEFT(COALESCE(STUFF(a.assemblyid, CHARINDEX('+', a.assemblyid), 1, ''), a.assemblyid), 2)
WHERE a.id = 'm'
  AND
  (
    LEFT(a.prun, 8) >= '20120101'
    OR a.prun IS NULL
  );

What is the difference between select statements before and after `UNION ALL`? — Alex, Jun 12 '18 at 06:46
Alex - It is exactly the same except if you would have noticed the difference in the where clause a.id = 'm' instead of 'p'. — Feivel, Jun 12 '18 at 08:40
Why not do `WHERE a.id IN( 'm', 'p' )` in a single query. Note: you will have to measure performance as sometimes a `UNION ALL` is quicker. — Alex, Jun 12 '18 at 12:08

score 0 · Answer 1 · answered Jun 12 '18 at 06:25

0

You can rewrite your select using CTE. It is more readable. Quoting docs:

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

Just a simple sample:

WITH 
 step1 as 
  ( select a+1 as x, b-1 as y
    from t
  ),
 step2 as
  ( select x*2 as i, y/2 as j
    from step1
  )
select i+j as r
from step2;

You can chain several sentences in this way.

answered Jun 12 '18 at 06:25

dani herrera

48,760
8
117
177

Is this going to reduce the sub queries above if we use CTE instead. In case if you noticed these sub queries are repetitive and been queried from the same base table. More than performance I am concerned about reducing the code or at least get it to best practice. – Feivel Jun 12 '18 at 08:43
In order to improve your sentence to match best practices: 1) Avoid correlated subquery ( a subquery for each row) 2) Write sentence in a readable mode 3) Check performance. About point 1: it is better to prepare data in a previous cte select and then join with a new one instead to use a subquery for each row. Also use windowed functions instead `SELECT TOP 1`. – dani herrera Jun 12 '18 at 09:09
I tried to avoid the correlated sub query and also attempted to convert it into a join but I think I lack some expertise in there. Also, if I chose to go with CTE how do I get it done in my case. Is it possible if you can give me an example based on the query that I posted. – Feivel Jun 12 '18 at 14:26

score 0 · Answer 2 · answered Jun 12 '18 at 06:43

In the case of:

SELECT TOP 1
    [some column]
FROM x.dbo.Info
WHERE orderno = a.orderno
    AND releaseno = a.releaseno
    AND status NOT LIKE 'backflus%'
    AND status NOT LIKE 'so%'
ORDER BY stamp DESC

you can try OUTER APPLY see this and this e.g.:

SELECT a.id,
   (
     SELECT TOP 1
      name
     FROM x.dbo.Info l
     WHERE orderno = a.orderno
       AND releaseno = a.releaseno
       AND stamp =
       (
       SELECT MIN(stamp)
       FROM x.dbo.Info
       WHERE orderno = l.orderno
         AND releaseno = l.releaseno
         AND status = 'Released'
       )
     ORDER BY stamp DESC
   ) [shop_name],
   c.line_no,
   a.status,
   d.family,
   SomeInfo.name AS [lastworkplace], --<-- Note the change
   SomeInfo.lstatus AS [laststatus] --<-- Note the change
FROM BI.dbo.tblz a -- this is a view (not sure if that matters)
  LEFT JOIN X.dbo.tblx b
    ON b.id = a.salesorder
  LEFT JOIN X.dbo.tbls c
    ON c.tranid = a.salesorder
     AND c.itemid = a.assemblyid
     AND c.serialnum = a.ordercode
  LEFT JOIN Z.dbo.tbli d
    ON d.prodline = LEFT(COALESCE(STUFF(a.assemblyid, CHARINDEX('+', a.assemblyid), 1, ''), a.assemblyid), 2)

    OUTER APPLY(  --<-- Note the extra join
        SELECT TOP 1
            *
        FROM x.dbo.Info
        WHERE orderno = a.orderno
            AND releaseno = a.releaseno
            AND status NOT LIKE 'backflus%'
            AND status NOT LIKE 'so%'
        ORDER BY stamp DESC
    ) AS SomeInfo
WHERE a.id = 'p'
  AND
  (
    LEFT(a.prun, 8) >= '20120101'
    OR a.prun IS NULL
  )

Thanks! Alex I will give this a try. I am also looking to reduce the code so that we only query the table once and get all the columns derived from it rather than having it queried each time in the select list. — Feivel, Jun 12 '18 at 08:46

Avoiding multiple repetitive sub queries that are being used to derive columns in the select

2 Answers2