SQL Tune!!
Guys. Couple of days back I had posted this and got it as a solution. But this peace of code takes hell lot of time and they predict that this would require 21 days to complete the full updation of 10 Million records which is very bad. How can I tune this peace?
DECLARE @l_oldest_effdate DATETIME
DECLARE @FolderID int
DECLARE cFOLDERIDS CURSOR FOR
SELECT DISTINCT folderid
FROM doctaba
WHERE folderid <> 'N/A'
OPEN cFOLDERIDS
FETCH NEXT FROM cFOLDERIDS INTO @FolderID
WHILE @@FETCH_STATUS = 0
BEGIN
-- get the oldest eff date for this folderid
SELECT @l_oldest_effdate = MIN(effrecdate)
FROM doctaba
WHERE folderid = @FolderID
UPDATE doctaba
SET primaryrow = 'Y'
WHERE folderid = @FolderID
AND effrecdate = l_oldest_effdate;
FETCH NEXT FROM cFOLDERIDS INTO @FolderID
END
CLOSE cFOLDERIDS
DEALLOCATE cFOLDERIDS
my questions are:-
can someone please modify it to use bulk collect and update and update using rowid. I've no clue about sql server if this were in oracle, i would've known what to do. Please help.
DECLARE @l_oldest_effdate DATETIME
DECLARE @FolderID int
DECLARE cFOLDERIDS CURSOR FOR
SELECT DISTINCT folderid
FROM doctaba
WHERE folderid <> 'N/A'
OPEN cFOLDERIDS
FETCH NEXT FROM cFOLDERIDS INTO @FolderID
WHILE @@FETCH_STATUS = 0
BEGIN
-- get the oldest eff date for this folderid
SELECT @l_oldest_effdate = MIN(effrecdate)
FROM doctaba
WHERE folderid = @FolderID
UPDATE doctaba
SET primaryrow = 'Y'
WHERE folderid = @FolderID
AND effrecdate = l_oldest_effdate;
FETCH NEXT FROM cFOLDERIDS INTO @FolderID
END
CLOSE cFOLDERIDS
DEALLOCATE cFOLDERIDS
my questions are:-
can someone please modify it to use bulk collect and update and update using rowid. I've no clue about sql server if this were in oracle, i would've known what to do. Please help.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Angel,
What about the rollback segments? this plain statemet would be able to update all the 2 Million records?
What about the rollback segments? this plain statemet would be able to update all the 2 Million records?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try something like this:
UPDATE d
SET primaryrow = 'Y'
From doctaba d
Inner Join (
Select folderid,
MIN(effrecdate) FirstDate
From doctaba
Group By
folderid) a On d.folderid = a.folderid
UPDATE d
SET primaryrow = 'Y'
From doctaba d
Inner Join (
Select folderid,
MIN(effrecdate) FirstDate
From doctaba
Group By
folderid) a On d.folderid = a.folderid
>What about the rollback segments? this plain statemet would be able to update all the 2 Million records?
this statement should, with the index in place, run below 1-2 minutes.
this statement should, with the index in place, run below 1-2 minutes.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
acperkins,
Missed the group by when typing.
Tim
Missed the group by when typing.
Tim
Tim,
And I originally missed the WHERE, so I guess we are even :)
Anthony
And I originally missed the WHERE, so I guess we are even :)
Anthony
UPDATE doctaba
SET primaryrow = 'Y'
WHERE folderid = @FolderID
AND effrecdate = l_oldest_effdate;
Surely it should be:
UPDATE doctaba
SET primaryrow = 'Y'
WHERE folderid = @FolderID
AND effrecdate = @l_oldest_effdate;
Not that that is going to make it run much faster. You need to lose the CURSOR there is simply no reason for it.