May 22, 2017 at 8:39 am
Good morning,
I have a store proc that uses cursor to update table with 5 million records. It's extremely slow and expensive. Is there way to remove the cursor and speed up the query (I use SQL 2008) Here's conditions: I need to update column ParentFlag with value from ParentID column based on DocType.column value If DocType="A" then set ParentFlag=ParentID; if DocType<>"A" move to the next record and check if DocType ="A" If so update ParentFlag for all records in this chain with ParentID value. If DocType="A" not found leave ParentFlag=NULL. Row to row linked by ParentID value
Below is Example:
Create Table #Test (
PK int IDENTITY(1,1) NOT NULL,
DocID Varchar(10),
ParentID Varchar(10),
DocType Varchar(10),
ParentFlag Varchar(10)
)
Insert into #Test(DocID, ParentID,DocType)
SELECT '146F','147F','S'
UNION ALL
SELECT '147S', '161S', 'S'
UNION ALL
SELECT '161S', 'R-12', 'S'
UNION ALL
SELECT 'R-12', 'AST', 'A'
UNION ALL
SELECT '3','4','W'
UNION ALL
SELECT '4','5', 'W'
UNION ALL
SELECT '9A', '55W','A'
Need help with update query
UPDATE #Test SET ParentFlag='R-12' WHERE PK=1
UPDATE #Test SET ParentFlag='R-12' WHERE PK=2
UPDATE #Test SET ParentFlag='R-12' WHERE PK=3
UPDATE #Test SET ParentFlag='R-12' where PK=4
UPDATE #Test SET PArentFlag='55W' where PK=7
Expected result
SELECT * from #Test
Thank you for your help
May 22, 2017 at 12:19 pm
It took me some time to understand your requirements, but I finally got them. In my experience, the best option for this is to use something called "set-based loop". This is very fast and would outperform other methods. The reason is that it's updating sets instead of updating row by agonizing row. Read more about it in this article: http://qa.sqlservercentral.com/articles/set-based+loop/127670/
Here's an example based on your sample data:
UPDATE #Test SET
ParentFlag = DocID
WHERE DocType = 'A';
WHILE @@ROWCOUNT <> 0
UPDATE t SET
ParentFlag = p.ParentFlag
FROM #Test t
JOIN #Test p ON t.ParentID = p.DocID
WHERE t.ParentFlag IS NULL
AND p.ParentFlag IS NOT NULL;
Note: This code won't work as you posted because row 1 has ParentID with F and row 2 has DocID with S.
May 30, 2017 at 8:15 pm
>> I have a store proc that uses cursor to update table with 5 million records [sic]. It's extremely slow and expensive. <<
actually, it’s worse than that. It lacks data integrity. Which you’ve done is built pointer chains in SQL, as if you were still writing an assembly language. Your whole approach is wrong. You talk about records, flags and linkage; none of those are relational terms! The term “parent” comes from linked lists used in the old navigational databases. You also don’t know the syntax for insertion, so you’re using the original Sybase syntax as it is 30+ years ago.
>> Is there way to remove the cursor and speed up the query (I use SQL 2008) Here's conditions: I need to update column parent_flg with value from parent_id column based on document_type. If document_type = "A" then set parent_flg = parent_id; if document_type <> ‘A’ move to the next record [sic] and check if document_type = ‘A’ If so update parent_flg for all records [sic] in this chain [sic] with parent_id value. If document_type = ‘A’ not found leave parent_flg = NULL. Row to row linked [sic: links!?] by parent_id value
Below is Example:
CREATE TABLE Documents
(document_id VARCHAR(10) NOT NULL PRIMARY KEY,
document_type CHAR(5)NOT NULL
CHECK(document_type IN (??????))
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 1),
CHECK (lft > rgt));
take a little time in Google to learn the Nested Set Model for hierarchies. When you convert from your pointer list to a set oriented model, you may find out that you have cycles and other design flaws. Just have to clean those up by yourself by hand. Sorry
>> Need help with update query<<
you are doing the wrong thing badly. An update is a statement not a query and there is a huge difference. Unlike Fortran and the older languages that your mimicking in SQL, we have a more set oriented approach to our predicate.
UPDATE Documents
SET parent_flag = 'R-12'
WHERE pk IN (1, 2, 3, 4);
UPDATE Documents
SET parent_flag = '55W'
WHERE PK = 7;
as an exercise, try to write this, using a CASE expression in a single update.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 30, 2017 at 9:01 pm
jcelko212 32090 - Tuesday, May 30, 2017 8:15 PM>> Need help with update query<<you are doing the wrong thing badly. An update is a statement not a query and there is a huge difference. Unlike Fortran and the older languages that your mimicking in SQL, we have a more set oriented approach to our predicate.
UPDATE Documents
SET parent_flag = 'R-12'
WHERE pk IN (1, 2, 3, 4);
UPDATE Documents
SET parent_flag = '55W'
WHERE PK = 7;as an exercise, try to write this, using a CASE expression in a single update.
FYI,
That's not set based. That's simply a condensed scalar group of updates. If you read my original solution, you'll see that there's no hard coded value assigned to the columns.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply