New answers tagged lock
A better index would be (assuming ORDER BY IdTrackerPosition DESC is correct, and the query should not specify ORDER BY [Date] DESC instead): CREATE UNIQUE INDEX i -- Choose a better name! ON dbo.TrackerPositions (TrackerId, IdTrackerPosition DESC) INCLUDE (Position, [Date], Speed, NbSatellites, Direction, HDOP); The execution plan should look ...
Your index should be on (TrackerId ASC, [Date] DESC) INCLUDE (Position) so that it can easily locate the most recent one for each Tracker. But I really don't like the query from EF. Edit: ...because "most recent" should mean "latest datetime", not "latest identity value"
A few things: Remove the set implicit_transactions off Actually, this is probably for the best as you don't want them on. This setting has nothing to do with a "top level transaction". When implicit_transactions are ON, INSERT and DELETE (and others) will auto-start a transaction. You do not need a BEGIN TRAN / COMMIT around a table variable. Not only does ...
@Jacob - I would say we've all had an unpleasant experience at some point in our lives, so don't beat yourself up. It's admirable that you're seeking guidance and learning from it. So hats off to you. Some key things to target might be the impacted rowcounts. If you're inserting into @idsToDelete, ideally you have a recordset around 1000 rows or ...
Top 50 recent answers are included