Combining Updates on Table Variable

Question

If it's possible, I would like to know how to combine the two UPDATE statements and whether this would save performance. Query currently executes in 3 seconds but will continue to grow.

Bonus points if you know of neater, more elegant filtering in my CASE and WHERE statements.

Thanks

DECLARE @PartRevision TABLE
( 
OriginalPart VARCHAR(50) NULL,
PartNum VARCHAR(50) NULL,
Revision VARCHAR(50) NULL,
[Language] VARCHAR(50) NULL,
Hierarchy VARCHAR(50) NULL
)
INSERT INTO @PartRevision
(
    OriginalPart,
    PartNum,
    Revision,
    [Language],
    Hierarchy
)
SELECT
part_no
,''
,''
,''
,''
FROM dbo.PartMaster
;

UPDATE @PartRevision
SET PartNum = SUBSTRING(OriginalPart,1,6) 
    ,Revision = SUBSTRING(RIGHT(OriginalPart, LEN(OriginalPart) - 6),1,2)
    ,[Language] = CASE 
                    WHEN LEN(RIGHT(OriginalPart, LEN(OriginalPart) - 6)) = 2 THEN ''
                    WHEN RIGHT(OriginalPart, LEN(OriginalPart) - 6) = 'DEV' THEN ''
                    ELSE RIGHT(RIGHT(OriginalPart, LEN(OriginalPart) - 6), (LEN(RIGHT(OriginalPart, LEN(OriginalPart) - 6)) -2))
                    END
FROM @PartRevision
--business designed/approved filter--
WHERE LEFT(OriginalPart, 1) NOT IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z')
         AND OriginalPart NOT IN ('40T', '45T', '46AJ', '2044', '16NA', '12NA', '86TCE', '86TNA', '86TAS', '82TNA', '66TNA', '643TH', '843TH', '99000', '61TNA', '3215NA', '4626NA', '7127NA', '9250NA', '63AJNA', '1056TH', '8841NA', '3219NA', '1044TH', '3226NA', '4632NA', '4740NA', '8831NA', '208922', '7135NA', '85AJNA', '3220NA', '201975B', '201520A', '200989B', '109271B', '1044THS', '200465B', '202207A', '201436A', '2PREPAY', '200545A', '1056THS', '111169B', '30AJENA', '202204B', '202140B', '200551B', '201452B', '310142B', '2p5 ARA', '202100A', '111170B', '218999A', '1256THS', '202061B', '107212F', '202698A', '201974B', '201114B')       
;
UPDATE @PartRevision
SET PartNum = CONCAT(PartNum, [Language])
FROM @PartRevision 
;

SELECT * FROM @PartRevision

`LEN(RIGHT(OriginalPart, LEN(OriginalPart) - 6)) = 2` is a lot like `LEN(OriginalPart) - 6) = 2`, is it not? — HABO, Jun 21 '18 at 00:04
`LEFT(OriginalPart, 1) NOT IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z')` can be written `OriginalPart not like '[a-z]%'`. Not sure what it will do to performance. — HABO, Jun 21 '18 at 00:10
A temporary table with an appropriate index, e.g. on `OriginalPart`, may improve performance relative to a table variable without an index. — HABO, Jun 21 '18 at 00:14
`RIGHT(RIGHT(OriginalPart, LEN(OriginalPart) - 6), (LEN(RIGHT(OriginalPart, LEN(OriginalPart) - 6)) -2))` suffers from some redundancy like the earlier comment. You don't need to keep creating a substring (with `Right()`) just to take the length of it (with `Len()`) and perform some arithmetic. Start unravelling it and see if you can get it down to one `Right()` and one `Len()`. (And my first comment has an extra `)` floating around. My bad.) — HABO, Jun 21 '18 at 00:20

score 0 · Answer 1 · answered Jun 22 '18 at 20:48

The end result. Solved the problem, but am still curious about optimizing the UPDATES.

BEGIN
DECLARE @PartRevision TABLE
( 
OriginalPart VARCHAR(50) NULL,
PartNum VARCHAR(50) NULL,
Revision VARCHAR(50) NULL,
[Language] VARCHAR(50) NULL,
Hierarchy VARCHAR(50) NULL
)

DECLARE @HierLookup TABLE
(
Hierarchy INT IDENTITY(1,1) PRIMARY KEY
,RevisionCode VARCHAR(10) NOT NULL
)

DECLARE @RevisionGen TABLE
(
Alphabet VARCHAR(10) NOT NULL,
BaseRevision VARCHAR(10) NULL
)

DECLARE @iAlphabet INT = 65
DECLARE @iBaseRevision INT = 65

--Start build of Hierarchy Lookup Table (@HierLookup)
--INSERT A-Z into @RevisionGen
WHILE @iAlphabet < 91
BEGIN
    INSERT INTO @RevisionGen
    (
        Alphabet
    )
    VALUES
    (
        CHAR(@iAlphabet)
    )
    SET @iAlphabet = @iAlphabet + 1;
END
;

--Final INSERTS and UPDATES to @HierLookup
WHILE @iBaseRevision < 91
BEGIN
    UPDATE @RevisionGen
    SET BaseRevision = CHAR(@iBaseRevision);
    ;
    INSERT INTO @HierLookup
    (
        RevisionCode
        --,Hierarchy
    )
    SELECT CONCAT(BaseRevision, Alphabet)
    FROM @RevisionGen;
    SET @iBaseRevision = @iBaseRevision + 1
    ;
END
;

--Starting INSERTS and UPDATES to @PartRevision
--Get base Part data from ETL Data Warehouse
INSERT INTO @PartRevision
(
    OriginalPart,
    PartNum,
    Revision,
    [Language],
    Hierarchy
)
SELECT
PART_NO
,''
,''
,''
,''
FROM man.Stage_Gue_InventoryPart
;

--Start parsing Parts data
UPDATE @PartRevision
SET PartNum = SUBSTRING(OriginalPart,1,6) 
    ,Revision = SUBSTRING(RIGHT(OriginalPart, LEN(OriginalPart) - 6),1,2)
    ,[Language] = CASE 
                    WHEN LEN(OriginalPart) - 6 = 2 THEN ''
                    WHEN RIGHT(OriginalPart, LEN(OriginalPart) - 6) = 'DEV' THEN ''
                    ELSE RIGHT(RIGHT(OriginalPart, LEN(OriginalPart) - 6), (LEN(OriginalPart) - 6) -2)
                    END
FROM @PartRevision
--business approved filter--
WHERE LEFT(OriginalPart, 1) NOT LIKE '[a-z]%'
         AND OriginalPart NOT IN ('40T', '45T', '46AJ', '2044', '16NA', '12NA', '86TCE', '86TNA', '86TAS', '82TNA', '66TNA', '643TH', '843TH', '99000', '61TNA', '3215NA', '4626NA', '7127NA', '9250NA', '63AJNA', '1056TH', '8841NA', '3219NA', '1044TH', '3226NA', '4632NA', '4740NA', '8831NA', '208922', '7135NA', '85AJNA', '3220NA', '201975B', '201520A', '200989B', '109271B', '1044THS', '200465B', '202207A', '201436A', '2PREPAY', '200545A', '1056THS', '111169B', '30AJENA', '202204B', '202140B', '200551B', '201452B', '310142B', '2p5 ARA', '202100A', '111170B', '218999A', '1256THS', '202061B', '107212F', '202698A', '201974B', '201114B', '216610')         
;

--Final update to Part Number
UPDATE @PartRevision
SET PartNum = CONCAT(PartNum, [Language])
FROM @PartRevision 
;

--INSERT Hierarchy numbers from @HierLookup table
UPDATE @PartRevision
SET Hierarchy = CASE 
                WHEN l.Hierarchy = '' THEN 1
                ELSE l.Hierarchy
                END 
FROM @PartRevision AS p
LEFT JOIN @HierLookup AS l
ON l.RevisionCode = p.Revision
;

--UPDATE ETL, Pre-Merge Table
UPDATE man.Stage_Gue_InventoryPart
SET PART_NO = CASE 
                WHEN PartNum = '' THEN OriginalPart
                ELSE PartNum
                END,
    ENG_REVISION = Revision
    --REV_SEQ = Hierarchy   
FROM @PartRevision
WHERE PART_NO = OriginalPart
;

END

Combining Updates on Table Variable

1 Answers1