May 10, 2017 at 6:52 am
Good morning,
I have a store proc that uses cursor to update table with 80 million records.Iyt's extremely slow (runs over an hour) and expensive. I'm trying to remove cursor and speed up the query (SQL 2008) I need help with update statment (Please see attached for code and test table)
Thank you
May 10, 2017 at 7:04 am
legeboka - Wednesday, May 10, 2017 6:52 AMGood morning,
I have a store proc that uses cursor to update table with 80 million records.Iyt's extremely slow (runs over an hour) and expensive. I'm trying to remove cursor and speed up the query (SQL 2008) I need help with update statment (Please see attached for code and test table)
Thank youIF (SELECT OBJECT_ID('tempdb..#Test50'))is not null
DROP TABLE #Test50
CREATE TABLE #Test50 (
PK INT IDENTITY(1,1) NOT NULL,
CustValue VARCHAR(2),
CustArea VARCHAR (50),
PKMarker INT
)INSERT INTO #Test50 (CustValue, CustArea)
SELECT 'ST', '70A'
UNION ALL
SELECT 'ST', '70B'
UNION ALL
SELECT 'ST', '80C'
UNION ALL
SELECT 'SS', '70B'
UNION ALL
SELECT 'SS', '70B'
UNION ALL
SELECT 'ST', '5Q'
UNION ALL
SELECT 'SS', '5Q'
UNION ALL
SELECT 'ST', '33A'
UNION ALL
SELECT 'AQ', '33A'
UNION ALL
SELECT 'SS', '33A'
UNION ALL
SELECT 'SS', '59A'--SQL update query need it that will update PKMarker column with PK value.
--Rules are: get PK where CustValue=ST and update PKMarker column where CustValue=SS and Cust area matches row with ST value
UPDATE #Test50 SET PKMarker=2 WHERE PK in(4,5)
UPDATE #Test50 SET PKMarker=6 WHERE PK in(7)
UPDATE #Test50 SET PKMarker=8 WHERE PK in(10)--Expected result
SELECT * from #Test50
This could be solved in a single select with a self-join.
UPDATE SS SET
PKMarker = ST.PK
FROM #Test50 AS SS
JOIN #Test50 AS ST ON SS.CustArea = ST.CustArea
WHERE SS.CustValue = 'SS'
AND ST.CustValue = 'ST';
Avoid posting code in attached files as many people won't even consider downloading them.
May 10, 2017 at 7:29 am
-- Depending on the size of CustValue * CustArea partitions,
-- it might be worthwhile picking just one row
UPDATE SS SET
PKMarker = x.PK
FROM #Test50 AS SS
CROSS APPLY (
SELECT TOP(1) PK
--SELECT PK = MIN(PK)
FROM #Test50 AS ST
WHERE ST.CustValue = 'ST'
AND ST.CustArea = SS.CustArea
) x
WHERE SS.CustValue = 'SS'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 10, 2017 at 7:34 am
ChrisM@Work - Wednesday, May 10, 2017 7:29 AM-- Depending on the size of CustValue * CustArea partitions,
-- it might be worthwhile picking just one row
UPDATE SS SET
PKMarker = x.PK
FROM #Test50 AS SS
CROSS APPLY (
SELECT TOP(1) PK
--SELECT PK = MIN(PK)
FROM #Test50 AS ST
WHERE ST.CustValue = 'ST'
AND ST.CustArea = SS.CustArea
) x
WHERE SS.CustValue = 'SS'
May 10, 2017 at 8:12 am
Thank you guys for your help. I missed one important condition. Value in CustArea column for SS record can be different. It can increase by 2 char at the time. I only need to mark records where LEN(ST)=LEN(SS) and ST=SS or SUBSTRING (SS, 1, LEN(ST))=ST. I don't need to mark records where LEN(SS)<LEN(ST). Please see attached for modified example. For some reason I can't paste example code in the window.
Once again, thank you for your help
May 10, 2017 at 8:25 am
legeboka - Wednesday, May 10, 2017 8:12 AMThank you guys for your help. I missed one important condition. Value in CustArea column for SS record can be different. It can increase by 2 char at the time. I only need to mark records where LEN(ST)=LEN(SS) and ST=SS or SUBSTRING (SS, 1, LEN(ST))=ST. I don't need to mark records where LEN(SS)<LEN(ST). Please see attached for modified example. For some reason I can't paste example code in the window.
Once again, thank you for your help
UPDATE SS SET
PKMarker = x.PK
FROM #Test50 AS SS
CROSS APPLY (
SELECT TOP(1) PK
--SELECT PK = MIN(PK)
FROM #Test50 AS ST
WHERE ST.CustValue = 'ST'
AND SS.CustArea LIKE ST.CustArea + '%'
) x
WHERE SS.CustValue = 'SS'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 10, 2017 at 10:18 am
Thank you ChrisM It's close. Record PK=6 should not be updated because size increased by 4 char instead of 2. I will see if I can correct this
May 10, 2017 at 11:13 am
legeboka - Wednesday, May 10, 2017 10:18 AMThank you ChrisM It's close. Record PK=6 should not be updated because size increased by 4 char instead of 2. I will see if I can correct this
You just need to change the wildcard for 2 single character wildcards.
UPDATE SS SET
PKMarker = ST.PK
FROM #Test50 AS SS
JOIN #Test50 AS ST ON SS.CustArea = ST.CustArea
OR SS.CustArea LIKE ST.CustArea + '__'
WHERE SS.CustValue = 'SS'
AND ST.CustValue = 'ST';
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply