There is a table FileView, which is built from other tables. It contains columns id and id_parent, with their help the file storage structure is built. The problem is that when the nesting is very large, there is not enough time to execute the query.

The function was originally written using CTE

WITH cte AS
(
    SELECT id, id_parent, id_project, file_name_cl, edit_date_time, is_directory, is_deleted, file_size, id_user_last_edit, creation_date, previous_version, attributes, last_access_time, id_file_blob, md5, crc32c, full_path
    FROM REMOTE_OFFICE_3.dbo.FilesView
    WHERE id = 14654306
    UNION ALL
    SELECT t.id, t.id_parent, t.id_project, t.file_name_cl, t.edit_date_time, t.is_directory, t.is_deleted, t.file_size, t.id_user_last_edit, t.creation_date, t.previous_version, t.attributes, t.last_access_time, t.id_file_blob, t.md5, t.crc32c, t.full_path
    FROM REMOTE_OFFICE_3.dbo.FilesView t
    JOIN cte ON cte.id = t.id_parent
)
SELECT *
FROM cte

Which took time with 140,000,000 files in the table
[2024-11-25 09:49:25] 7,682 rows retrieved starting from 1 in 9 m 58 s 636 ms (execution: 1 s 425 ms, fetching: 9 m 57 s 211 ms)

A procedure based on a temporary table was tested

CREATE PROCEDURE GetFileDescendantsProc
@itemID INT
AS
BEGIN
    CREATE TABLE #TempGetFileDescendantsProc (
                                                 id INT,
                                                 id_parent INT,
                                                 id_project INT,
                                                 file_name_cl NVARCHAR(1000),
                                                 edit_date_time DATETIME,
                                                 is_directory BIT,
                                                 is_deleted BIT,
                                                 file_size BIGINT,
                                                 id_user_last_edit INT,
                                                 creation_date DATETIME,
                                                 previous_version INT,
                                                 attributes INT,
                                                 last_access_time DATETIME,
                                                 id_file_blob INT,
                                                 md5 NVARCHAR(32),
                                                 crc32c BIGINT,
                                                 full_path NVARCHAR(4000),
                                                 id_project_stage INT,
                                                 id_kit INT
    );

    INSERT INTO #TempGetFileDescendantsProc
    SELECT *
    FROM FilesView
    WHERE id = @itemID;

    WHILE EXISTS (SELECT 1 FROM #TempGetFileDescendantsProc r JOIN FilesView f ON r.id = f.id_parent WHERE f.id NOT IN (SELECT id FROM #TempGetFileDescendantsProc))
        BEGIN
            INSERT INTO #TempGetFileDescendantsProc
            SELECT f.*
            FROM FilesView f
                     JOIN #TempGetFileDescendantsProc r ON r.id = f.id_parent
            WHERE f.id NOT IN (SELECT id FROM #TempGetFileDescendantsProc);
        END;

    SELECT * FROM #TempGetFileDescendantsProc;
END;
go

Showed poor results in terms of resource allocation by creating a huge temporary table

Also tried via LIKE

CREATE FUNCTION GetFileDescendants
(
    @itemID INT
)
    RETURNS @Result TABLE
                    (
                        id INT,
                        id_parent INT,
                        id_project INT,
                        file_name_cl nvarchar(1000),
                        edit_date_time DATETIME,
                        is_directory BIT,
                        is_deleted BIT,
                        file_size bigint,
                        id_user_last_edit INT,
                        creation_date DATETIME,
                        previous_version INT,
                        attributes INT,
                        last_access_time DATETIME,
                        id_file_blob INT,
                        md5 nvarchar(32),
                        crc32c bigint,
                        full_path nvarchar(4000),
                        id_project_stage INT,
                        id_kit INT
                    )
AS
BEGIN
    DECLARE @RootPath NVARCHAR(MAX);

    SELECT @RootPath = full_path
    FROM REMOTE_OFFICE_3.dbo.FilesView
    WHERE id = @itemID;

    INSERT INTO @Result
    SELECT *
    FROM REMOTE_OFFICE_3.dbo.FilesView t
    WHERE full_path LIKE @RootPath + '%';

    RETURN;
END
go

I would like to see your methods to solve the problem.