June 11, 2018 at 3:06 pm
I've been wrapping my head around this 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
);
June 12, 2018 at 1:40 am
I am guessing you are asking this because the query is slow or you find it unreadable?
If the later, you can always just do multiple joins to the Info view instead, if the former, I would be more worried about the join
LEFT(COALESCE(STUFF(a.assemblyid, CHARINDEX('+', a.assemblyid), 1, ''), a.assemblyid), 2)
as just ouch!
Best way to go about this is to keep the original and just develop a copy alongside it, then test, test, test until the output is identical for all user cases.
I would also have a look at tblz to ensure that is efficient enough..
June 12, 2018 at 2:37 am
Rick-153145 - Tuesday, June 12, 2018 1:40 AMI am guessing you are asking this because the query is slow or you find it unreadable?
If the later, you can always just do multiple joins to the Info view instead, if the former, I would be more worried about the join
LEFT(COALESCE(STUFF(a.assemblyid, CHARINDEX('+', a.assemblyid), 1, ''), a.assemblyid), 2)
as just ouch!
Best way to go about this is to keep the original and just develop a copy alongside it, then test, test, test until the output is identical for all user cases.
I would also have a look at tblz to ensure that is efficient enough..
More than performance I would like to get rid of the unneeded sub queries to reduce the length of code and more importantly to get rid of the duplication. Any specifics on how I can convert that into multiple joins or if there is best practice reference concerning sub queries etc. that can be taken into consideration to improvise on the code.
June 12, 2018 at 3:23 am
The first thing to do is look at your data. Is there a reason for using TOP 1 all over the place? As the values are coming from Info and the joins are mostly the same, you can probably get all this from a single or a couple of joins, but that is very dependant on your data. Things to check first are:
- Are there duplicates?
- Can you get ShopName, LastWorkPlace and LastStatus in one query?
- If so, can you make that a view/a couple of views that you can then use in place of all the separate sub queries?
- Can you join tblz directly to Info?
- Can you change tblz to put out an extra field to cover prodline in tbli?
- What datatype is tblz.prun and why are you using it as a date if its not a date datatype, can you change it to be a date?
So, lets start with a couple of the queries:
This:
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]
Is the same as:
SELECT
name AS [lastworkplace],
lstatus AS [laststatus]
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
Assuming that TOP 1 is superfluous. If TOP 1 is required, you could easily change it to a join, so:
SELECT
a.id,
info.name AS [lastworkplace],
info.lstatus AS [laststatus],
.......
FROM tblz a
LEFT JOIN (SELECT name, lstatus FROM (SELECT ROWNUMBER() OVER (PARTITION BY OrderNo, ReleaseNo ORDER BY stamp DESC) as rownum, Name, lstatus, orderno, releaseno WHERE status NOT IN (<x>,<y>)) a WHERE rownum = 1) Info
on Info.orderno = a.orderno and Info.releaseno = a.releaseno
........
What would be better though is if you had a statusid rather than the text of status, then you could change the query to NOT IN(x,y)
June 12, 2018 at 8:12 am
Here's a rewrite of the first query (before the UNION ALL). I don't have time to do both but they look very similar. The second two subqueries can be combined into 1 query, as below.
SELECT a.id,
oa1.[shop_name],
c.line_no,
a.status,
d.family,
oa2.[lastworkplace],
oa2.[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)
OUTER APPLY
(
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
) AS [oa1]
OUTER APPLY
(
SELECT TOP (1)
i.name AS [lastworkplace], i.lstatus AS [laststatus]
FROM x.dbo.Info i
WHERE i.orderno = a.orderno
AND i.releaseno = a.releaseno
AND i.status NOT LIKE 'backflus%'
AND i.status NOT LIKE 'so%'
ORDER BY i.stamp DESC
) AS [oa2]
WHERE a.id = 'p'
AND
(
LEFT(a.prun, 8) >= '20120101'
OR a.prun IS NULL
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply