April 25, 2008 at 3:05 am
I have a stored procedure that is taking about 12 hours to run, even when the database has no other connections.
If anyone can find a better way to achieve my goal in less time I would be most appreciative, as our window of available time when we upgrade the production server will be a several hours only.
It may be of note that the table we are updating is very wide - about 40 columns, maybe 10 of which are UNIQUEIDENTIFIER type.
The names of objects have been changed to protect the innocent. 😎
CREATE PROCEDURE SetFKID
AS
SET NOCOUNT ON;
DECLARE @l_ID UNIQUEIDENTIFIER
DECLARE @l_FK2ID UNIQUEIDENTIFIER
DECLARE @l_cur CURSOR
SET @l_cur = CURSOR FAST_FORWARD FOR
SELECT ID, FK2ID
FROM MyTable
WHERE
FKID = '00000000-0000-0000-0000-000000000000' AND
FK2ID <> '00000000-0000-0000-0000-000000000000'
OPEN @l_cur
FETCH @l_cur INTO @l_ID, @l_FK2ID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE MyTable
SET FKID = (SELECT FKID FROM MyFK2Table WHERE ID = @l_FK2ID)
WHERE ID = @l_ID
FETCH @l_cur INTO @l_ID, @l_FK2ID
END
If you are curious, we are denormalising this table for performance reasons - the DB is part of a high volume live production system.
CLOSE @l_cur
DEALLOCATE @l_cur
April 25, 2008 at 3:22 am
Um, maybe trash the cursor and do it as a set-based update. Shouldn't take more than an hour...
Untested, because I don't have schema, sample data or expected output. Should be enough to give you an idea though....
Edit. On second thoughts....
UPDATE MyTable
SET FKID = Source.FKID
FROM MyTable INNER JOIN MyTable Source on MyTable.FK2ID = Source.ID
WHERE MyTable.FKID = '00000000-0000-0000-0000-000000000000' AND
MyTable.FK2ID <> '00000000-0000-0000-0000-000000000000')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2008 at 3:31 am
Did you try the set-based approach.
[Code]
-- avoid implicit rewrites by adding a nullable column
alter table Mytable add colmigrated datetime NULL; -- to keep track if data has already been processed
go
declare @RowsAtATime int
set @RowsAtATime = 50000
Declare @Rowsupdated int
Set @Rowsupdated = @RowsAtATime
While @Rowsupdated = @RowsAtATime
begin
UPDATE top (RowsAtATime ) T
set FKID = T2.FKID
, colmigrated = getdate() -- to keep track if data has already been processed
from MyTable T
inner join MyFK2Table T2
on T2.ID = T.FK2ID
WHERE T.FKID = '00000000-0000-0000-0000-000000000000' AND
T.FK2ID <> '00000000-0000-0000-0000-000000000000'
and colmigrated is null
end
/* only if full processing finished
alter table Mytable drop colmigrated ;
*/
[/Code]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 25, 2008 at 4:34 am
Many thanks! I went with GilaMonster's query and it's scary how fast it was.
I thought there may be some issue doing a join on 8 million rows of two wide tables, but the server didn't bat an eyelid. 🙂
April 25, 2008 at 5:35 am
Great. There's just one thing I wanna know...
How fast was it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2008 at 5:44 am
To replace the functionality 100%, we should use LEFT JOIN.
The correlated subquery might return NULL if there is no match.
UPDATEmt
SETmt.FKID = x.FKID
FROMMyTable AS mt
LEFT JOINMyFK2Table AS x ON x.ID = mt.FK2ID
WHEREmt.FKID = '00000000-0000-0000-0000-000000000000'
AND mt.FK2ID > '00000000-0000-0000-0000-000000000000'
N 56°04'39.16"
E 12°55'05.25"
April 25, 2008 at 6:00 am
GilaMonster (4/25/2008)
Great. There's just one thing I wanna know...How fast was it?
Well, I exited the previous query with 1.4 million rows left to do (instead of 8.4 million originally).
It took about 5 mins to do the remaining 1.4 million rows.
April 25, 2008 at 6:03 am
Peso (4/25/2008)
To replace the functionality 100%, we should use LEFT JOIN.The correlated subquery might return NULL if there is no match.
UPDATEmt
SETmt.FKID = x.FKID
FROMMyTable AS mt
LEFT JOINMyFK2Table AS x ON x.ID = mt.FK2ID
WHEREmt.FKID = '00000000-0000-0000-0000-000000000000'
AND mt.FK2ID > '00000000-0000-0000-0000-000000000000'
In this case there is a relationship between the tables, and the FK2ID column can not be null, so we know it has to return a value.
April 25, 2008 at 6:12 am
dw (4/25/2008)
Peso (4/25/2008)
To replace the functionality 100%, we should use LEFT JOIN.The correlated subquery might return NULL if there is no match.
UPDATEmt
SETmt.FKID = x.FKID
FROMMyTable AS mt
LEFT JOINMyFK2Table AS x ON x.ID = mt.FK2ID
WHEREmt.FKID = '00000000-0000-0000-0000-000000000000'
AND mt.FK2ID > '00000000-0000-0000-0000-000000000000'
In this case there is a relationship between the tables, and the FK2ID column can not be null, so we know it has to return a value.
Rule no 1: tell your system what you know !
If you know you only want to process matching data, use an INNER JOIN.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 25, 2008 at 7:58 am
OP didn't state if the Fk was a constraint or not.
And since OP used a correlated subquery in his original post, I had to raise a flag for this issue.
If OP had told that there were a fk-constraint present, GilaMionster's answer would be (and is) the proper solution.
Later he wrote there was a fk-relationship, which I interpret as a fk-constraint.
N 56°04'39.16"
E 12°55'05.25"
April 25, 2008 at 8:39 am
Peso (4/25/2008)
OP didn't state if the Fk was a constraint or not.And since OP used a correlated subquery in his original post, I had to raise a flag for this issue.
If OP had told that there were a fk-constraint present, GilaMionster's answer would be (and is) the proper solution.
Later he wrote there was a fk-relationship, which I interpret as a fk-constraint.
I take your point that I didn't mention the constraint, but it's a pretty stupid thing *not* to have an FK constraint on an FK field...
Mind you, Assumption is the Mother of all F**kups, as they say, so you were right not to assume.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply