Optimizing the Sql Server 2019 Query [ About Categories with Id and ParentId Relation using single column in table]

Question

How can i optimize the Sql Server Query

Table name is: Pro_itemmaster
Column name is: itm_Code

My Query take 17 seconds to complete

Query

    ; WITH CatItem AS(
    SELECT
        PIM.itm_Code AS Id
        ,CASE WHEN LEN(PIM.itm_Code) = 2 THEN NULL ELSE LEFT(PIM.itm_Code, LEN(PIM.itm_Code) - CHARINDEX('-',REVERSE(PIM.itm_Code))) END AS ParentId
        ,1 AS [Depth]
    FROM
        Pro_itemmaster AS PIM
    WHERE 
        LEN(PIM.itm_Code) = 2

    UNION ALL
        
    SELECT
        PIM.itm_Code AS Id
        ,CASE WHEN LEN(PIM.itm_Code) = 2 THEN NULL ELSE LEFT(PIM.itm_Code, LEN(PIM.itm_Code) - CHARINDEX('-',REVERSE(PIM.itm_Code))) END AS ParentId
        ,[CatItem].[Depth] + 1 AS [Depth]
    FROM
        [Pro_itemmaster] AS [PIM]
    JOIN
        [CatItem]
    ON
        CASE WHEN LEN(PIM.itm_Code) = 2 THEN NULL ELSE LEFT(PIM.itm_Code, LEN(PIM.itm_Code) - CHARINDEX('-',REVERSE(PIM.itm_Code))) END = CatItem.Id
    )

    SELECT * FROM CatItem

Query Execution Plan

Don't provide a link to a file sharing site for others to download; we won't as we know the dangers of downloading files from complete strangers on the internet. If you want to provide sample data do so as DDL and DML statements. Don't forgot to include your query plan via Paste The Plan as well. Also, what does this question have to do with C#? — Thom A, Jun 04 '22 at 11:43
Your query isn't SARGable, so there is, in truth, little you can do to improve it without a change to the underlying design. Of course, we have no sample data or expected results, so difficult to suggest. — Thom A, Jun 04 '22 at 11:56

score 0 · Answer 1 · edited Jun 04 '22 at 23:38

0

I am able to reduce the time to 3 seconds by introducing a table variable.

The issue here is that I cannot modify the main table and add ParentId column so needed the work around.

DECLARE @DataTable TABLE (Id VARCHAR(60) PRIMARY KEY NOT NULL, ParentId VARCHAR(60))

INSERT INTO @DataTable
SELECT itm_Code
    , CASE WHEN LEN(itm_Code) = 2 THEN NULL ELSE LEFT(itm_Code, LEN(itm_Code) - CHARINDEX('-',REVERSE(itm_Code))) END
FROM Pro_itemmaster

; WITH CatItem AS(
    SELECT
        PIM.Id
        ,PIM.ParentId
        ,1 AS [Depth]
    FROM
        @DataTable AS PIM
    WHERE 
        LEN(PIM.Id) = 2

    UNION ALL
    
    SELECT
        PIM.Id
        ,PIM.ParentId
        ,[CatItem].[Depth] + 1 AS [Depth]
    FROM
        @DataTable AS [PIM]
    JOIN
        [CatItem]
    ON
        PIM.ParentId = CatItem.Id
)
SELECT *
FROM CatItem
ORDER BY Id

edited Jun 04 '22 at 23:38

Dale K

25,246
15
42
71

answered Jun 04 '22 at 12:35

Muhammad Waqas Aziz

766
7
8

2

The semicolon is a statement **terminator**. Use them consistently and correctly and you don't need this lazy kluge of forcing it in front of "WITH" as a "beginator". Learn good habits, not bad ones. In addition, the name PIM does not need delimiting (with braces) - do not use braces without need (and one can argue there should never be a need to use them). – SMor Jun 04 '22 at 12:53
2

That isn't a temporary table, it's a table **variable**; a completely different thing. You `CREATE` a temporary table, rather than `DECLARE` it (just like a table, not a variable) and it's name is prefixed with a hash (`#`) character, not an at (`@`) like variables. – Thom A Jun 04 '22 at 13:07
1

Note best practice is to list the columns you are inserting into. – Dale K Jun 04 '22 at 23:39

Optimizing the Sql Server 2019 Query [ About Categories with Id and ParentId Relation using single column in table]

1 Answers1