Why SQL query takes too long time to execute?

Question

when I executed this, at the first speed was good but now is taking long time and speed is very slow . I don't know why but I think maybe I should clear cache. I want say is taking about 1 day but not finish 20% yet. actually it's about SELECT on a TABLE in database [spt] and INSERT in another database [AB_Warehouse] but both of them is on one Server .

Notice : one filed in database [spt] we have data like this "aa+tt+bb+rr+yy" and I splited with function StringSplitXML and each of them insert to [Workspaces_Tbl] Level1...15

this is my Query :

USE [spt]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

BEGIN    
    DECLARE @ID decimal(20, 0);
    DECLARE @parent_id decimal(20, 0);
    DECLARE @Type int;
    DECLARE @workspacetype_id decimal(10, 0);
    DECLARE @isvirtual decimal(1, 0);
    DECLARE @enable decimal(1, 0);
    DECLARE @Level1 nvarchar(MAX);
    DECLARE @Level2 nvarchar(MAX);
    DECLARE @Level3 nvarchar(MAX);
    DECLARE @Level4 nvarchar(MAX);
    DECLARE @Level5 nvarchar(MAX);
    DECLARE @Level6 nvarchar(MAX);
    DECLARE @Level7 nvarchar(MAX);
    DECLARE @Level8 nvarchar(MAX);
    DECLARE @Level9 nvarchar(MAX);
    DECLARE @Level10 nvarchar(MAX);
    DECLARE @Level11 nvarchar(MAX);
    DECLARE @Level12 nvarchar(MAX);
    DECLARE @Level13 nvarchar(MAX);
    DECLARE @Level14 nvarchar(MAX);
    DECLARE @Level15 nvarchar(MAX);
    DECLARE @titles_tmp nvarchar(MAX);

    DECLARE @cont_spilit_tittle int;
    DECLARE @parent_titles_tmp nvarchar(MAX);
    DECLARE @cont_tmp int;
    DECLARE @cont int;
    SELECT @cont = COUNT(*) FROM septa.dbo.workspaces;
    SET @cont_tmp = 0;

    While(@cont_tmp<@cont)
    BEGIN  
        SET @ID = (SELECT spt.dbo.workspaces.workspace_id FROM septa.dbo.workspaces ORDER BY workspace_id ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @parent_id = (SELECT septa.dbo.workspaces.parent_id FROM septa.dbo.workspaces ORDER BY workspace_id ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @workspacetype_id = (SELECT septa.dbo.workspaces.workspacetype_id FROM septa.dbo.workspaces ORDER BY workspace_id ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @isvirtual = (SELECT septa.dbo.workspaces.isvirtual FROM septa.dbo.workspaces ORDER BY workspace_id ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @enable = (SELECT septa.dbo.workspaces.enable FROM septa.dbo.workspaces ORDER BY workspace_id ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @titles_tmp = (SELECT septa.dbo.workspaces.title FROM septa.dbo.workspaces ORDER BY workspace_id ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)
        SET @parent_titles_tmp = (SELECT septa.dbo.workspaces.parent_titles FROM septa.dbo.workspaces ORDER BY workspace_id ASC OFFSET @cont_tmp ROWS FETCH NEXT 1 ROWS ONLY)

        IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL DROP TABLE #MyTempTable
        SELECT IDENTITY(int, 1, 1) AS 'RowID',* into #MyTempTable 
        FROM StringSplitXML(@parent_titles_tmp,'+')

        insert into #MyTempTable VALUES (@titles_tmp)

        SET @cont_spilit_tittle = (SELECT COUNT(*) FROM #MyTempTable)

        IF(@cont_spilit_tittle<0)SET @cont_spilit_tittle = 1

        SET @Type = @cont_spilit_tittle

        WHILE(@cont_spilit_tittle < 15)
        BEGIN
            insert into #MyTempTable VALUES ('')
            SET @cont_spilit_tittle = CAST(@cont_spilit_tittle AS INT) + 1
        END

        SET @Level1 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level2 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level3 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level4 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 3 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level5 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 4 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level6 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 5 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level7 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 6 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level8 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 7 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level9 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 8 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level10 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 9 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level11 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 10 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level12 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 11 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level13 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 12 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level14 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 13 ROWS FETCH NEXT 1 ROWS ONLY)
        SET @Level15 = (SELECT Value FROM #MyTempTable ORDER BY RowID ASC OFFSET 14 ROWS FETCH NEXT 1 ROWS ONLY)


        INSERT INTO [AB_Warehouse].[dbo].[Workspaces_Tbl]
           ([ID]
           ,[parent_id]
           ,[Type]
           ,[workspacetype_id]
           ,[isvirtual]
           ,[enable]
           ,[Level1]
           ,[Level2]
           ,[Level3]
           ,[Level4]
           ,[Level5]
           ,[Level6]
           ,[Level7]
           ,[Level8]
           ,[Level9]
           ,[Level10]
           ,[Level11]
           ,[Level12]
           ,[Level13]
           ,[Level14]
           ,[Level15])
     VALUES
            (
            @ID,
            @parent_id,
            @Type,
            @workspacetype_id,
            @isvirtual,
            @enable,
            @Level1,
            @Level2,
            @Level3,
            @Level4,
            @Level5,
            @Level6,
            @Level7,
            @Level8,
            @Level9,
            @Level10,
            @Level11,
            @Level12,
            @Level13,
            @Level14,
            @Level15
            ) 

        SET @cont_tmp = CAST(@cont_tmp AS INT) + 1
    END  


 RETURN
END

Answer 1

As a tip towards the way of thinking how to solve this issue.

select t.id, t.title, l.*
from test t
cross apply (
  select 
    p.cnt [Level-Count], 
    ISNULL(p.[Level-1], '') [Level-1], 
    ISNULL(p.[Level-2], '') [Level-2],
    ISNULL(p.[Level-3], '') [Level-3],
    ISNULL(p.[Level-4], '') [Level-4]
  from (
    select 
      s.value, 
      'Level-' + cast(row_number() over (order by (select 1)) as varchar(10)) rn,
      count(*) over () - CASE WHEN t.parent_list = '' THEN 1 ELSE 0 END cnt
    from STRING_SPLIT(
      t.parent_list 
      + CASE WHEN t.parent_list = '' THEN '' ELSE '+' END 
      + cast(t.id as varchar(10))
      + CASE WHEN t.parent_list = '' THEN '+' ELSE '' END
      , '+'
    ) s
  ) s
  pivot ( 
    max(s.value) for s.rn in (
      [Level-1], 
      [Level-2], 
      [Level-3], 
      [Level-4]) 
  ) p
) l

Something like that could replace the whole script you've posted. Some loop may still remain for the purpose of processing these data in portions. But the most part of code is unnecessary (temp tables and so on).

| id |        title | Level-Count | Level-1 | Level-2 | Level-3 | Level-4 |
|----|--------------|-------------|---------|---------|---------|---------|
|  1 |       item 1 |           1 |       1 |         |         |         |
|  2 |     item 1/2 |           2 |       1 |       2 |         |         |
|  3 |       item 3 |           1 |       3 |         |         |         |
|  4 |     item 3/4 |           2 |       3 |       4 |         |         |
|  5 |   item 3/4/5 |           3 |       3 |       4 |       5 |         |
|  6 | item 3/4/5/6 |           4 |       3 |       4 |       5 |       6 |
|  7 |     item 2/7 |           3 |       1 |       2 |       7 |         |
|  8 |       item 8 |           1 |       8 |         |         |         |

http://sqlfiddle.com/#!18/7aa71/31