August 4, 2021 at 2:44 pm
Is there a better way to write this nested join query? I'm concerned about the performance, b/c I'm basically forcing the order of the join. I need to get a list of shipments and join wsPKG records only if the wsPKG record has a matching wsPKGLin record.
select s.Shipment_No, p.pkg_no, l.ord_no
from wsPKGShipment s
left join wsPKG p
inner join wsPKGLin l ON p.PKG_ID = l.PKG_ID
ON s.Shipment_No = p.Shipment_No
where s.Shipment_No = 169200
August 4, 2021 at 3:18 pm
Might this work?
select s.Shipment_No, p.pkg_no, l.ord_no -- You need to get ord_no from either s or p instead
from wsPKGShipment s
left join wsPKG p
ON s.Shipment_No = p.Shipment_No
where s.Shipment_No = 169200 and exists (select 1 from wsPKGLin l where p.PKG_ID = l.PKG_ID)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2021 at 3:40 pm
If I understand your requirements correctly, the code below will do what you want.
As to performance, make sure the s and p tables have an index on Shipment_No and that the l table has an index on PKG_ID. I don't want to get more specific on the indexes yet because the query shown might not contain all columns.
SELECT s.Shipment_No,
CASE WHEN l.PGK_ID IS NULL THEN NULL ELSE p.pkg_no END AS pkg_no,
l.ord_no
FROM dbo.wsPKGShipment s
LEFT OUTER JOIN dbo.wsPKG p ON s.Shipment_No = p.Shipment_No
LEFT OUTER JOIN dbo.wsPKGLin l ON p.PKG_ID = l.PKG_ID
WHERE s.Shipment_No = 169200
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 4, 2021 at 3:50 pm
Might this work?
select s.Shipment_No, p.pkg_no, l.ord_no -- You need to get ord_no from either s or p instead
from wsPKGShipment s
left join wsPKG p
ON s.Shipment_No = p.Shipment_No
where s.Shipment_No = 169200 and exists (select 1 from wsPKGLin l where p.PKG_ID = l.PKG_ID)
Ord_No only exists on the wsPKGLin record.
August 4, 2021 at 3:55 pm
If I understand your requirements correctly, the code below will do what you want.
As to performance, make sure the s and p tables have an index on Shipment_No and that the l table has an index on PKG_ID. I don't want to get more specific on the indexes yet because the query shown might not contain all columns.
SELECT s.Shipment_No,
CASE WHEN l.PGK_ID IS NULL THEN NULL ELSE p.pkg_no END AS pkg_no,
l.ord_no
FROM dbo.wsPKGShipment s
LEFT OUTER JOIN dbo.wsPKG p ON s.Shipment_No = p.Shipment_No
LEFT OUTER JOIN dbo.wsPKGLin l ON p.PKG_ID = l.PKG_ID
WHERE s.Shipment_No = 169200
That wouldn't guarantee that a wsPKG record has a matching wsPKGLin record. Or at least I'm pretty sure that's right.
August 4, 2021 at 3:58 pm
ScottPletcher wrote:If I understand your requirements correctly, the code below will do what you want.
As to performance, make sure the s and p tables have an index on Shipment_No and that the l table has an index on PKG_ID. I don't want to get more specific on the indexes yet because the query shown might not contain all columns.
SELECT s.Shipment_No,
CASE WHEN l.PGK_ID IS NULL THEN NULL ELSE p.pkg_no END AS pkg_no,
l.ord_no
FROM dbo.wsPKGShipment s
LEFT OUTER JOIN dbo.wsPKG p ON s.Shipment_No = p.Shipment_No
LEFT OUTER JOIN dbo.wsPKGLin l ON p.PKG_ID = l.PKG_ID
WHERE s.Shipment_No = 169200That wouldn't guarantee that a wsPKG record has a matching wsPKGLin record. Or at least I'm pretty sure that's right.
As I said, I wasn't sure I understood what you wanted, it seemed contradictory. "I need to get a list of shipments and join wsPKG records only if the wsPKG record has a matching wsPKGLin record." That sounded like you wanted to list all shipments, regardless, but only list wsPKG data if there was a match in wsPKGLin. So that's what the query above does.
Change the JOINs to INNER if you only want to list when there is a row match all the way across.
Please keep in mind, we have NO idea what your data structures and requirements are other than what you tell us about them.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 4, 2021 at 4:34 pm
Let me try to explain what i need better. I want all shipments, regardless of whether they have matching wsPKG or wsPKGlin records. I want to join wsPKG records to the shipment records, but only if the wsPKG record has a matching wsPKGlin record.
If i use a left join on the wsPKGlin record, i will get wsPKG records that don't have a matching wsPKGlin record, which i don't want.
August 4, 2021 at 4:42 pm
Thanks for that, now I'm clear.
Yeah, I think the first query you posted should do that.
As to performance, make sure the s and p tables have an index on Shipment_No and that the l table has an index on PKG_ID. On the indexes on the p and l tables, INCLUDE all columns needed to make the index covering for the query.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 4, 2021 at 4:55 pm
Thanks for that, now I'm clear.
Yeah, I think the first query you posted should do that.
As to performance, make sure the s and p tables have an index on Shipment_No and that the l table has an index on PKG_ID. On the indexes on the p and l tables, INCLUDE all columns needed to make the index covering for the query.
Sorry for the initial confusion. You don't see any issues that could come from forcing the wsPKG and wsPKGlin join order using the nested join?
August 4, 2021 at 5:02 pm
ScottPletcher wrote:Thanks for that, now I'm clear.
Yeah, I think the first query you posted should do that.
As to performance, make sure the s and p tables have an index on Shipment_No and that the l table has an index on PKG_ID. On the indexes on the p and l tables, INCLUDE all columns needed to make the index covering for the query.
Sorry for the initial confusion. You don't see any issues that could come from forcing the wsPKG and wsPKGlin join order using the nested join?
No, assuming SQL can still optimize the query to include the restrictions on wsPGK based on the WHERE condition on the s table column. If not, then you're right, we may need to further adjust the code to prevent all rows in wsPGK and *Lin table from being joined.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 4, 2021 at 7:42 pm
This subquery actually seems to work well. Any ideas if it would have any performance benefits over the nested join query?
select s.Shipment_No, pl.pkg_no, pl.ord_no
from wsPKGShipment s
left join (
select p.Shipment_No, p.PKG_No, l.Ord_no
from wsPKG p
inner join wsPKGLin l ON p.PKG_ID = l.PKG_ID
) pl ON s.Shipment_No = pl.Shipment_No
where s.Shipment_No = 169200
August 4, 2021 at 8:06 pm
I would think the subquery would (almost) certainly be able to take advantage of the fact that the query only needs to lookup a single Shipment_No, rather than processing all rows in wsPKG and wsPGKLin. That is why it performs so well overall. You can look at the query plan to confirm that.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 4, 2021 at 8:09 pm
I would think the subquery would (almost) certainly be able to take advantage of the fact that the query only needs to lookup a single Shipment_No, rather than processing all rows in wsPKG and wsPGKLin. That is why it performs so well overall. You can look at the query plan to confirm that.
Yes, the query plans are almost identical, same scan counts and logical reads, except the subquery plan has 2 compute scalar operations that don't seem to have any effect on the execution time.
August 4, 2021 at 8:15 pm
Yeah, those scalar ops can be ignored as far as performance goes, as long as they aren't repeated a million times or so.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 5, 2021 at 9:13 pm
You could also use OUTER APPLY or a CTE. The derived table (sub-query) is essentially the same thing as using a CTE - I personally like the CTE construct better, but that is a personal preference.
Select s.Shipment_No
, w.pkg_no
, w.ord_no
From wsPKGShipment s
Outer Apply (Select * -- list just the columns needed here
From wsPKG p
Inner Join wsPKGLin l On p.PKG_ID = l.PKG_ID
Where p.Shipment_No = s.Shipment_No
) w
Where s.Shipment_No = 169200;
Not sure if the OUTER APPLY will perform any better - just showing another option.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply