sql - Hiearchical Join takes long time -

sql - Hiearchical Join takes long time -

i have tree hierarchical construction representing product structure. on each product level (6 levels), have salesprice linked it. using 2 tables joined each other link prices on lower levels prices on level above. want don't take business relationship cost more once. done next code (notice utilize level 0, 1 , 2 show idea):

select l0_salesprice ,l1_salesprice ,l2_salesprice (select distinct a.* bct bring together quotationline ql on a.pricecalcid = ql.pricecalcid a.levels = 0) l0 bring together (select distinct a.* bct bring together quotationline ql on a.pricecalcid = ql.pricecalcid a.levels = 1) l1 on l0.itemid = l1.parentitemid bring together (select distinct a.* bct bring together quotationline ql on a.pricecalcid = ql.pricecalcid a.levels = 2) l2 on l1.itemid = l2.parentitemid

the problem query never finishes executing, , out of memory error.

table bct 750 000 rows , table quotationline 22000 rows.

any advice appreciated.

to demonstrate how troubleshoot issue, here sample table definitions.

create table [description] ( [descriptionid] int identity not null constraint [pk_description] primary key, [descriptiontext] nvarchar(50) not null ) go create table [hierarchy] ( [hierarchyid] int not null constraint [pk_hierarchy] primary key, [parenthierarchyid] int null constraint [fk_hierarchy_parenthierarchyid] references [hierarchy] ([hierarchyid]) on delete no action on update no action, [price] money not null, [descriptionid] int null constraint [fk_hierarchy_description] references [description] ([descriptionid]) on delete set null on update cascade ) go create index [ix_hierarchy_parenthierarchyid] on [hierarchy] ([parenthierarchyid]) include ([hierarchyid], [price], [descriptionid]) go

a naive way hierarchy -- is, 1 unlikely solve performance issue -- might be:

;with rowends ( select h.[hierarchyid], h.[parenthierarchyid], h.[price], h.[descriptionid], h.[hierarchyid] [rowendhierarchyid], 0 [reverselevel] [hierarchy] h not exists (select 1 [hierarchy] i.[parenthierarchyid] = h.[hierarchyid]) union select h.[hierarchyid], h.[parenthierarchyid], h.[price], h.[descriptionid], r.[rowendhierarchyid], r.[reverselevel] + 1 [reverselevel] [hierarchy] h inner bring together rowends r on h.[hierarchyid] = r.[parenthierarchyid] ), inorder ( select r.rowendhierarchyid, r.[hierarchyid], r.price, d.descriptiontext, rank() on (partition r.[rowendhierarchyid] order r.[reverselevel] desc) [level] rowends r left bring together [description] d on r.descriptionid = d.descriptionid ) select distinct o.rowendhierarchyid, p.[1] price1, d.[1] description1, p.[2] price2, d.[2] description2, p.[3] price3, d.[3] description3, p.[4] price4, d.[4] description4, p.[5] price5, d.[5] description5, p.[6] price6, d.[6] description6, p.[7] price7, d.[7] description7 inorder o inner bring together (select projp.rowendhierarchyid, projp.[level], projp.[price] inorder projp) ppre pivot (min([price]) [level] in ([1], [2], [3], [4], [5], [6], [7])) p on o.rowendhierarchyid = p.rowendhierarchyid left bring together (select projd.rowendhierarchyid, projd.[level], projd.descriptiontext inorder projd) dpre pivot (min(descriptiontext) [level] in ([1], [2], [3], [4], [5], [6], [7])) d on o.rowendhierarchyid = d.rowendhierarchyid order o.rowendhierarchyid

this example, of course, uses recursive mutual table look hierarchy. rather starting @ root of tree , working toward leaves, query takes opposite approach. advantage doing each row in output corresponds leaf node in tree.

performance, however, may still unsatisfactory approach because have no chance index mutual table expressions, result sets may quite large. provided tempdb has sufficient space , performance, following, more-verbose query may improve performance.

create table #rowend ( [rowendhierarchyid] int not null, [hierarchyid] int not null, [parenthierarchyid] int null, [price] money not null, [descriptionid] int null, [reverselevel] int not null, primary key ([rowendhierarchyid], [reverselevel] desc) ) create index [ix_rowend_parenthierarchyid] on #rowend ([parenthierarchyid], [rowendhierarchyid], [reverselevel]) create index [ix_rowend_reverselevel] on #rowend ([reverselevel] desc, [parenthierarchyid], [rowendhierarchyid]) insert #rowend ([hierarchyid], [parenthierarchyid], [price], [descriptionid], [rowendhierarchyid], [reverselevel]) select h.[hierarchyid], h.[parenthierarchyid], h.[price], h.[descriptionid], h.[hierarchyid], 1 [hierarchy] h not exists (select 1 [hierarchy] i.parenthierarchyid = h.[hierarchyid]) declare @reverselevel int set @reverselevel = 0 while exists (select 1 #rowend re re.reverselevel > @reverselevel) begin set @reverselevel = @reverselevel + 1 insert #rowend ([hierarchyid], [parenthierarchyid], [price], [descriptionid], [rowendhierarchyid], [reverselevel]) select h.[hierarchyid], h.[parenthierarchyid], h.[price], h.[descriptionid], re.[rowendhierarchyid], @reverselevel + 1 [hierarchy] h inner bring together #rowend re on re.parenthierarchyid = h.[hierarchyid] , re.reverselevel = @reverselevel end create table #price ( rowendhierarchyid int not null primary key, [1] money null, [2] money null, [3] money null, [4] money null, [5] money null, [6] money null, [7] money null ) insert #price (rowendhierarchyid, [1], [2], [3], [4], [5], [6], [7]) select p.rowendhierarchyid, p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7] (select re.rowendhierarchyid, re.price, rank() on (partition re.rowendhierarchyid order re.reverselevel desc) [level] #rowend re) ppre pivot (min([price]) [level] in ([1], [2], [3], [4], [5], [6], [7])) p create table #description ( rowendhierarchyid int not null primary key, [1] nvarchar(50) null, [2] nvarchar(50) null, [3] nvarchar(50) null, [4] nvarchar(50) null, [5] nvarchar(50) null, [6] nvarchar(50) null, [7] nvarchar(50) null ) insert #description (rowendhierarchyid, [1], [2], [3], [4], [5], [6], [7]) select d.rowendhierarchyid, d.[1], d.[2], d.[3], d.[4], d.[5], d.[6], d.[7] (select re.rowendhierarchyid, dt.descriptiontext, rank() on (partition re.rowendhierarchyid order re.reverselevel desc) [level] #rowend re left bring together [description] dt on re.descriptionid = dt.descriptionid) dpre pivot (min([descriptiontext]) [level] in ([1], [2], [3], [4], [5], [6], [7])) d select p.rowendhierarchyid, p.[1] price1, d.[1] description1, p.[2] price2, d.[2] description2, p.[3] price3, d.[3] description3, p.[4] price4, d.[4] description4, p.[5] price5, d.[5] description5, p.[6] price6, d.[6] description6, p.[7] price7, d.[7] description7 #price p inner bring together #description d on p.rowendhierarchyid = d.rowendhierarchyid order p.rowendhierarchyid drop table #description drop table #price drop table #rowend

the basic logic of obtaining hierarchy similar prior version. indexing temp tables in manner, however, improve query performance considerably.

sql sql-server tree structure