sql server - Optimization of TSQL CTE Recursion Query -
i have following cte uses recursion fetch level of each node in hierarhcy , in end, since have 27 levels, grabbing name of each of level since final user not interested in seeing guids.
emptree ( select e.dwh_dim_tfs_file_dwh_file_guid, cast(cast(e.dwh_dim_tfs_file_dwh_file_guid binary(4)) varbinary(max)) emphier, 1 employeelevel [dwh].[dim_tfs_file_view] e e.dwh_dim_tfs_file_dwh_fileparent_guid null union select c.dwh_dim_tfs_file_dwh_file_guid, cast(p.emphier + cast(c.dwh_dim_tfs_file_dwh_file_guid binary(4)) varbinary(max)), employeelevel +1 employeelevel emptree p join [dwh].[dim_tfs_file_view] c on c.dwh_dim_tfs_file_dwh_fileparent_guid = p.dwh_dim_tfs_file_dwh_file_guid ) select top 100 percent dwh_dim_tfs_file_dwh_file_guid ,employeelevel ,(select [file_name] [dwh].[dim_tfs_file_view] pu nullif(cast(substring(emphier, 1, 4) int), 0) = pu.dwh_dim_tfs_file_dwh_file_guid) level1 ,(select [file_name] [dwh].[dim_tfs_file_view] pu nullif(cast(substring(emphier, 1, 9) int), 0) = pu.dwh_dim_tfs_file_dwh_file_guid) level2 ,(select [file_name] [dwh].[dim_tfs_file_view] pu nullif(cast(substring(emphier, 1, 13) int), 0) = pu.dwh_dim_tfs_file_dwh_file_guid) level3 emptree order dwh_dim_tfs_file_dwh_file_guid i have 27 levels... view grabbing info has 3 indexes: (parent,child) (parent) (child)
this table has 200m rows , growing.. query extremely slow, think of cause of "name grabbing" per level...
is more efficient way achieve result? maybe through joins?
please if can
thanks!!
not getting proper recursions, fetching anchor section.
with emptree ( select e.dwh_dim_tfs_file_dwh_fileparent_guid,e.dwh_dim_tfs_file_dwh_file_guid, 1 depth, file_name_string = cast(cast(e.file_name binary(100)) varbinary(8000)) [dbo].[hierarchy_luis] e e.dwh_dim_tfs_file_dwh_fileparent_guid null union select e.dwh_dim_tfs_file_dwh_fileparent_guid,e.dwh_dim_tfs_file_dwh_file_guid, p.depth +1 depth, file_name_string = cast(concat(p.file_name_string, cast(e.file_name binary(100))) varbinary(8000)) [dbo].[hierarchy_luis] e join emptree p on e.dwh_dim_tfs_file_dwh_fileparent_guid = p.dwh_dim_tfs_file_dwh_file_guid ) select p.dwh_dim_tfs_file_dwh_file_guid, p.depth, level01 =cast(substring(p.file_name_string, 1, 100) nvarchar(100)), level02 =cast(substring(p.file_name_string, 101, 100) nvarchar(100)), level03 =cast(substring(p.file_name_string, 201, 100) nvarchar(100)), level04 =cast(substring(p.file_name_string, 301, 100) nvarchar(100)), level05 =cast(substring(p.file_name_string, 401, 100) nvarchar(100)), level07 =cast(substring(p.file_name_string, 501, 100) nvarchar(100)), level08 =cast(substring(p.file_name_string, 601, 100) nvarchar(100)), level09 =cast(substring(p.file_name_string, 701, 100) nvarchar(100)), level10 =cast(substring(p.file_name_string, 801, 100) nvarchar(100)), level11 =cast(substring(p.file_name_string, 901, 100) nvarchar(100)), level12 =cast(substring(p.file_name_string, 1001, 100) nvarchar(100)), level13 =cast(substring(p.file_name_string, 1101, 100) nvarchar(100)), level14 =cast(substring(p.file_name_string, 1201, 100) nvarchar(100)), level15 =cast(substring(p.file_name_string, 1301, 100) nvarchar(100)), level16 =cast(substring(p.file_name_string, 1401, 100) nvarchar(100)), level17 =cast(substring(p.file_name_string, 1501, 100) nvarchar(100)), level18 =cast(substring(p.file_name_string, 1601, 100) nvarchar(100)), level19 =cast(substring(p.file_name_string, 1701, 100) nvarchar(100)), level20 =cast(substring(p.file_name_string, 1801, 100) nvarchar(100)), level21 =cast(substring(p.file_name_string, 1901, 100) nvarchar(100)), level22 =cast(substring(p.file_name_string, 2001, 100) nvarchar(100)), level23 =cast(substring(p.file_name_string, 2101, 100) nvarchar(100)), level24 =cast(substring(p.file_name_string, 2201, 100) nvarchar(100)), level25 =cast(substring(p.file_name_string, 2301, 100) nvarchar(100)), level26 =cast(substring(p.file_name_string, 2401, 100) nvarchar(100)), level27 =cast(substring(p.file_name_string, 2501, 100) nvarchar(100)) emptree p
without having representative test data , expected outcome, it's difficult nail down specifics... following should in ball part though...
if object_id('tempdb..#dim_tfs_file_view', 'u') not null drop table #dim_tfs_file_view; go create table #dim_tfs_file_view ( dwh_dim_tfs_file_dwh_file_guid int not null, dwh_dim_tfs_file_dwh_fileparent_guid int null, [file_name] uniqueidentifier not null ); insert #dim_tfs_file_view (dwh_dim_tfs_file_dwh_file_guid, dwh_dim_tfs_file_dwh_fileparent_guid, [file_name]) select dwh_dim_tfs_file_dwh_file_guid = t.n, dwh_dim_tfs_file_dwh_fileparent_guid = case when t.n = 1 null else isnull(nullif(mi.managerid -1, 0), 1) end, [file_name] = newid() dbo.tfn_tally(1000, 1) t cross apply ( values (abs(checksum(newid())) % t.n + 1) ) mi (managerid); -- select * #dim_tfs_file_view dtfv; cte_recursion ( select e.dwh_dim_tfs_file_dwh_file_guid, nodelevel = 1, file_name_string = cast(cast(e.[file_name] binary(16)) varbinary(8000)) #dim_tfs_file_view e e.dwh_dim_tfs_file_dwh_fileparent_guid null union select e.dwh_dim_tfs_file_dwh_file_guid, nodelevel = r.nodelevel + 1, file_name_string = cast(concat(r.file_name_string, cast(e.[file_name] binary(16))) varbinary(8000)) cte_recursion r join #dim_tfs_file_view e on r.dwh_dim_tfs_file_dwh_file_guid = e.dwh_dim_tfs_file_dwh_fileparent_guid ) select r.dwh_dim_tfs_file_dwh_file_guid, r.nodelevel, level01 = nullif(cast(substring(r.file_name_string, 1, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level02 = nullif(cast(substring(r.file_name_string, 17, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level03 = nullif(cast(substring(r.file_name_string, 33, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level04 = nullif(cast(substring(r.file_name_string, 49, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level05 = nullif(cast(substring(r.file_name_string, 65, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level06 = nullif(cast(substring(r.file_name_string, 81, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level07 = nullif(cast(substring(r.file_name_string, 97, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level08 = nullif(cast(substring(r.file_name_string, 113, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level09 = nullif(cast(substring(r.file_name_string, 129, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level10 = nullif(cast(substring(r.file_name_string, 145, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level11 = nullif(cast(substring(r.file_name_string, 161, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level12 = nullif(cast(substring(r.file_name_string, 177, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level13 = nullif(cast(substring(r.file_name_string, 193, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level14 = nullif(cast(substring(r.file_name_string, 209, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level15 = nullif(cast(substring(r.file_name_string, 225, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level16 = nullif(cast(substring(r.file_name_string, 241, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level17 = nullif(cast(substring(r.file_name_string, 257, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level18 = nullif(cast(substring(r.file_name_string, 273, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level19 = nullif(cast(substring(r.file_name_string, 289, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level20 = nullif(cast(substring(r.file_name_string, 305, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level21 = nullif(cast(substring(r.file_name_string, 321, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level22 = nullif(cast(substring(r.file_name_string, 337, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level23 = nullif(cast(substring(r.file_name_string, 353, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level24 = nullif(cast(substring(r.file_name_string, 369, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level25 = nullif(cast(substring(r.file_name_string, 385, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level26 = nullif(cast(substring(r.file_name_string, 401, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000'), level27 = nullif(cast(substring(r.file_name_string, 417, 16) uniqueidentifier), '00000000-0000-0000-0000-000000000000') cte_recursion r; a sample of output...
dwh_dim_tfs_file_dwh_file_guid nodelevel level01 level02 level03 level04 level05 level06 level07 level08 level09 level10 level11 level12 level13 level14 level15 level16 level17 level18 level19 level20 level21 level22 level23 level24 level25 level26 level27 ------------------------------ ----------- ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------ 1 1 f7ccfa67-f558-41ee-b1bf-8d96692ab99f null null null null null null null null null null null null null null null null null null null null null null null null null null 2 2 f7ccfa67-f558-41ee-b1bf-8d96692ab99f 34caf6d3-44ba-4de1-9e03-836ee38c43e3 null null null null null null null null null null null null null null null null null null null null null null null null null 3 2 f7ccfa67-f558-41ee-b1bf-8d96692ab99f a69d9be6-d83a-40ab-9d5b-04219317a3f9 null null null null null null null null null null null null null null null null null null null null null null null null null 
Comments
Post a Comment