Quel est le moyen le plus efficace de concaténer une chaîne de caractères à partir de toutes les lignes parentales en utilisant T-SQL ?

Question

J'ai une table qui a une clé étrangère auto-référencée qui représente sa ligne parent. Pour illustrer le problème dans sa forme la plus simple, nous allons utiliser cette table :

CREATE TABLE Folder(
    id int IDENTITY(1,1) NOT NULL, --PK
    parent_id int NULL,        --FK
    folder_name varchar(255) NOT NULL)

Je veux créer une fonction à valeur scalaire qui renverrait une chaîne concaténée du nom du dossier et de tous ses noms de dossiers parents jusqu'au dossier racine, qui serait désigné par une valeur parent_id nulle.

Ma solution actuelle est une approche procédurale qui, je le suppose, n'est pas idéale. Voici ce que je fais :

CREATE FUNCTION dbo.GetEntireLineage
    (@folderId INT)
    RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @lineage VARCHAR(MAX)
    DECLARE FolderId INT

    SELECT @lineage = folder_name, FolderId = parent_id FROM Folder WHERE id = @folderId

WHILE NOT FolderId IS NULL
    BEGIN
        SET FolderId = (SELECT parent_id FROM Folder WHERE parent_id = FolderId)
        SET @lineage = (SELECT @lineage + '-' + (SELECT folder_name FROM Folder WHERE parent_id = FolderId))
    END
RETURN @lineage
END

Existe-t-il un moyen plus idéal de procéder ? Je suis un programmeur expérimenté mais T-SQL n'est pas un monde familier pour moi et je sais que ces problèmes nécessitent généralement une approche différente en raison de la nature des données basées sur des ensembles. Si vous m'aidez à trouver une solution ou si vous me donnez d'autres conseils et astuces pour utiliser T-SQL, je vous en serai reconnaissant.

Answer 1

Pour être sûr des performances, il faut faire des tests. J'ai effectué quelques tests en utilisant votre version (légèrement modifiée) et une version CTE récursive suggérée par d'autres.

J'ai utilisé votre tableau d'exemple avec 2048 lignes dans une seule hiérarchie de dossiers. Ainsi, en passant 2048 comme paramètre à la fonction, 2048 concaténations sont effectuées.

La version en boucle :

create function GetEntireLineage1 (@id int)
returns varchar(max)
as
begin
  declare @ret varchar(max)

  select @ret = folder_name,
         @id = parent_id
  from Folder
  where id = @id

  while @@rowcount > 0
  begin
    select @ret = @ret + '-' + folder_name,
           @id = parent_id
    from Folder
    where id = @id
  end
  return @ret
end

Statistiques :

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 122 ms.

La version récursive du CTE :

create function GetEntireLineage2(@id int)
returns varchar(max)
begin
  declare @ret varchar(max);

  with cte(id, name) as
  (
    select f.parent_id,
           cast(f.folder_name as varchar(max))
    from Folder as f
    where f.id = @id
    union all
    select f.parent_id,
           c.name + '-' + f.folder_name
    from Folder as f
      inner join cte as c
        on f.id = c.id
  )
  select @ret = name
  from cte
  where id is null
  option (maxrecursion 0)

  return @ret
end

Statistiques :

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 183 ms.

Entre les deux, c'est donc la version en boucle qui est la plus efficace, du moins sur mes données de test. Vous devez tester sur vos données réelles pour en être sûr.

Modifier

CTE récursif avec for xml path('') truc.

create function [dbo].[GetEntireLineage4](@id int)
returns varchar(max)
begin
  declare @ret varchar(max) = '';

  with cte(id, lvl, name) as
  (
    select f.parent_id,
           1,
           f.folder_name
    from Folder as f
    where f.id = @id
    union all
    select f.parent_id,
           lvl + 1,
           f.folder_name
    from Folder as f
      inner join cte as c
        on f.id = c.id
  )
  select @ret = (select '-'+name
                 from cte
                 order by lvl
                 for xml path(''), type).value('.', 'varchar(max)')
  option (maxrecursion 0)

  return stuff(@ret, 1, 1, '')
end

Statistiques :

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 37 ms.

Answer 2

Utiliser un requête récursive pour traverser les parents et ensuite cette méthode pour la concaténation dans une chaîne de caractères.

Answer 3

Un identifiant de hiérarchie est souvent inutile, à moins que vous ne disposiez d'une hiérarchie vraiment profonde ou de très grands ensembles de données qui peuvent tirer parti de l'indexation. C'est le plus rapide que vous puissiez obtenir sans modifier votre schéma.

 with recursiveCTE (parent_id,concatenated_name) as (
    select parent_id,folder_name
    from folder
    union all
    select f.parent_id,r.concatenated_name +f.folder_name
    from folder f
    inner join recursiveCTE r on r.parent_id = f.id
    )
    select folder_name from recursiveCTE

Answer 4

Cela fonctionne pour vous :

 with cte (Parent_id, Path) as 
    (
    select Parent_Id,Folder_Name
    from folder
    union all
    select f.Parent_Id,r.Path + '\' + f.Folder_Name
    from Folder as f
    inner join cte as c on c.Parent_Id = f.Id
    )
    select Folder_Name from cte