Self-join Update query taking too long to run

Question

Self-join Update query taking too long to run

arti.aga16

SSC Rookie

Points: 42
More actions
May 15, 2018 at 11:04 pm

#410494

Hello,
I have a query that runs on daily basis and keeps on looping itself and takes too long to run.
Table is quite huge with millions of records in it.I am posting the query below:
The columns in the table originally are are --employeeid, BusUnitID, SkillID, Status, Severity, StartDate, BackgroundStatus
alter table fOSMHistory
add RowID int identity,
    FromRowID int,
    LastRowID int,
    LastStatus varchar(10),
    LastSeverity int,ApprLeaveID int;
--Find the last Colour status before each Blue End Date
Update fOSMHistory
set LastStatus = cs.Status,
    LastSeverity = cs.Severity
from fOSMHistory cs join fOSMHistory ce -- Colour Start and Colour end
    on cs.RowID = ce.LastRowID
join fOSMHistory -- Blue Ends
    on fOSMHistory.Status = 'BlueEnd'
    and cs.employeeid = fOSMHistory.employeeid
    and cs.SkillID = fOSMHistory.SkillID
    and fOSMHistory.StartDate between cs.StartDate and ce.StartDate
The highlighted query takes too long to run.
We are thinking to do the changes as below :
1.PK_fOSMHistory PRIMARY KEYCLUSTERED on RowID
2. IX_fOSMHistory_FromRowID NONCLUSTERED INDEX on FromRowID, include Status
3. IX_fOSMHistory_LastRowID NONCLUSTERED INDEX on LastRowID, include Status
4. IX_fOSMHistory_EmployeeID_SkillID NONCLUSTERED INDEX on EmployeeID, includeSkillID
Can someone please suggest any other changes that will speed up the query?
Thanks,
Arti

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

You say it's looping on itself, but i don't see any loops. I imagine part of the problem is the possible Cartesian product you have by not using an alias in your UPDATE clause (it also makes it very unclear what side of the table you're updating). If you're doing up UPDATE, and updating a table in your FROM, you should alias both and then you should use the alias in the UPDATE clause too. Something like this (assuming you're trying to update the previously unaliased table, which I've now aliased h):

UPDATE h SET LastStatus = cs.[Status], LastSeverity = cs.Severity FROM fOSMHistory cs JOIN fOSMHistory ce ON cs.RowID = ce.LastRowID -- Colour Start and Colour end JOIN fOSMHistory h ON h.[Status] = 'BlueEnd' -- Blue Ends AND cs.employeeid = h.employeeid AND cs.SkillID = h.SkillID AND h.StartDate BETWEEN cs.StartDate AND ce.StartDate;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Reformatted, the query might be better rewritten to use a Windows function. Can you post up some representative sample data to code against?

UPDATE fOSMHistory SET 
 LastStatus = cs.[Status],
 LastSeverity = cs.Severity

FROM fOSMHistory fOSMHistory

JOIN fOSMHistory cs -- Blue Ends
ON cs.employeeid = fOSMHistory.employeeid
AND cs.SkillID = fOSMHistory.SkillID

JOIN fOSMHistory ce -- Colour Start and Colour end
ON ce.LastRowID = cs.RowID

WHERE fOSMHistory.[Status] = 'BlueEnd'
AND fOSMHistory.StartDate BETWEEN cs.StartDate AND ce.StartDate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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