December 28, 2018 at 11:53 am
Is there a best practices way to join a table to itself for a parent child relationship?
We have 2 tables to join; pkg and pkglines. Some pkg records have a parent record in the pkg table. i.e. pkg.ID = pkg.ParentID.
I need to join all the pkgline records to the pkg records by pkg.ID. Some pkg records tie directly to the pkgline records if they don't have any children.
But if the pkg record has children, only the pkg children records tie to the pkgline records. I also need to account for cases where there are more than one nested pkg child record.
We would never have more than 3 nested pkg children records, so i don't think recursion would be an issue.
We would need to select the pkgID from the parent pkg record along with the rest of the columns from the pkgline record(s).
December 28, 2018 at 12:17 pm
Jackie Lowery - Friday, December 28, 2018 11:53 AMIs there a best practices way to join a table to itself for a parent child relationship?
We have 2 tables to join; pkg and pkglines. Some pkg records have a parent record in the pkg table. i.e. pkg.ID = pkg.ParentID.
I need to join all the pkgline records to the pkg records by pkg.ID. Some pkg records tie directly to the pkgline records if they don't have any children.
But if the pkg record has children, only the pkg children records tie to the pkgline records. I also need to account for cases where there are more than one nested pkg child record.
We would never have more than 3 nested pkg children records, so i don't think recursion would be an issue.We would need to select the pkgID from the parent pkg record along with the rest of the columns from the pkgline record(s).
Please provide the usual DDL, sample data (in the form of INSERT statements) & desired results.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 28, 2018 at 12:57 pm
Phil Parkin - Friday, December 28, 2018 12:17 PMPlease provide the usual DDL, sample data (in the form of INSERT statements) & desired results.
Let me know if I've made any mistakes.
create table wspkg (
pkg_Id int,
parent_Id int,
pkg_Type char(3),
status char(1),
bin_no char(15)
)
create table wspkglin (
pkglin_Id int,
pkg_Id int,
item_no char(30),
item_desc char(30),
qty decimal(13,4)
)
INSERT INTO wsPKG ( PKG_ID, ParentID, PKG_Type, Status, Bin_no )
VALUES
(1,NULL,'PLT','N', 'SHIP'),
(2,NULL,'PLT','N', 'SHIP'),
(3,1,'CTN','N', 'SHIP'),
(4,3,'CTN','N', 'SHIP'),
(5,1,'CTN','N', 'SHIP'),
(6,2,'CTN','N', 'SHIP'),
(7,NULL,'CTN','N', 'SHIP')
INSERT INTO wsPKGLin ( PKGLin_ID, PKG_ID, Item_no, Item_desc, Qty )
values
(1,4,'SH13','Shoe Size 13', 50),
(2,5,'DP5','Diapers Size 5', 20),
(3,6,'WIPE','Baby Wipes', 500),
(4,7,'SHTXL','Shirt XL', 30)
RESULTS
wsPKG.PKG_ID, wsPKG.PKG_Type, wsPKG.Status, wsPKG.Bin_no, wsPKGLin.Item_no, wsPKGLin.Item_Desc, wsPKGLin.Qty
1 , 'PLT','N', 'SHIP', 'SH13','Shoe Size 13', 50
1 , 'PLT','N', 'SHIP', 'DP5','Diapers Size 5', 20
2 , 'PLT','N', 'SHIP', 'WIPE','Baby Wipes', 500
7 , 'CTN','N', 'SHIP', 'SHTXL','Shirt XL', 30
December 30, 2018 at 6:18 am
I've come up with the following way to get the list of pkg master IDs with a recursive CTE. My question now is, why does it take nearly 4 sec to return a record based on the pkg_no, but if i filter by pkg_id the query only takes 2ms.
Here is the CTE and table Indexes involved. Also, I'm attaching both query execution plans.
CTE:
;with pkgMasters
AS
(
select p1.PKG_ID, p1.PKG_ID masterID
from wsPKG p1
where p1.ParentID IS NULL
UNION ALL
select p2.PKG_ID, pm.masterID
FROM wsPKG p2
INNER JOIN pkgMasters pm ON p2.ParentID = pm.PKG_ID
WHERE p2.ParentID IS NOT NULL
)
select p.PKG_No, p.PKG_ID, p.PKG_Type, p.Bin_no, pl.Item_no, pl.Item_desc, pl.Qty
from wsPKG p
JOIN pkgMasters pm ON p.PKG_ID = pm.masterID
JOIN wsPKGlin pl ON pm.PKG_ID = pl.PKG_ID
--WHERE p.pkg_id = '88F0F252-2FFB-479E-A857-001C3BD2F669'
WHERE p.PKG_No = 65572
wspkg indexes:
idxBatchPrintUdf7 nonclustered, unique located on PRIMARY UDF7, PKG_No
nciwsPKGStatus nonclustered, unique located on PRIMARY Status, PKG_ID
ncxParentIDPkgID nonclustered located on PRIMARY ParentID
ncxUDF3PID nonclustered, unique located on PRIMARY UDF3, PKG_ID
wsPKG0 clustered, unique, unique key located on PRIMARY PKG_No
wsPKG1 nonclustered located on PRIMARY Shipment_No
wsPKG10 nonclustered located on PRIMARY UDF15
wsPKG2 nonclustered located on PRIMARY Org_Source
wsPKG3 nonclustered located on PRIMARY Bin_no, Status
wsPKG4 nonclustered located on PRIMARY Shipment_No, PKG_ID
wsPKG5 nonclustered located on PRIMARY PKG_ID
wsPKG6 nonclustered located on PRIMARY ParentID
wsPKG7 nonclustered located on PRIMARY Shipment_No, PKG_Type, PKG_Seq_No
wsPKG8 nonclustered located on PRIMARY PKG_ID, Bin_no
wsPKG9 nonclustered located on PRIMARY PKG_ID, Shipment_No, Status
wsPKGLin indexes:
ncxPkgIDPkgLin nonclustered, unique located on PRIMARY PKGLin_ID, PKG_ID
wsPKGLin0 clustered, unique, unique key located on PRIMARY ID
wsPKGLin1 nonclustered located on PRIMARY PKG_ID
wsPKGLin2 nonclustered located on PRIMARY PKGLin_ID
wsPKGLin3 nonclustered located on PRIMARY Ord_no, Line_no, Seq_no, Item_no, Loc
wsPKGLin4 nonclustered located on PRIMARY UDF15
December 30, 2018 at 1:53 pm
Something else I've noticed. The query that runs fast has does a seek on the p1 alias with 2 seek predicates ( parentID = null, pkg_id = {pkg_id in where clause} )
When i use the pkg_no in the where clause, the an index seek is done on p1 with only 1 predicate ( parentID = null ).
Why isn't the second predicate passed into the seek when i use pkg_no in the Where clause?
January 2, 2019 at 11:30 am
Jackie Lowery - Friday, December 28, 2018 11:53 AM
Please post DDL and follow ANSI/ISO standards when asking for help.
January 2, 2019 at 12:44 pm
your DDL is incorrect. The data doesn't match the sample query you sent. Please provide relevant DDL and sample data and test it before posting
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply