Refactoring Legacy T-SQL for Improved Performance by Lisa Bohm

Author:Lisa Bohm
Language: eng
Format: epub
ISBN: 9781484255810
Publisher: Apress

SELECT tb.id

, u.Reputation

, u.DownVotes

FROM @theTable tb

INNER JOIN dbo.Users u ON tb.id = u.Id;

Listing 5-10Update DownVotes for multiple users

We saw the before results in Table 5-8. The after results are shown in Table 5-9.Table 5-9Results of code from Listing 5-10

Id

Reputation

DownVotes

1010297

24

15

1639596

0

4

2179513

4

4

2491405

0

4

2549795

30

4

Oh no! Each of these people had their Reputation decreased. When we look at the EXISTS statements in Listing 5-1, we’re checking to see if a record exists where the DownVotes are divisible by 5. However, we do NOT check for the same criteria in the actual update statement! If we look in the Triggerlog table using the code in Listing 5-5, we’ll also see a record for each of these users being run in the trigger.

If we modify the update statement in the trigger in Listing 5-1 using the code in Listing 5-11, we should only update the same records the EXISTS statement finds, and our results should be in much better shape.UPDATE u

SET u.Reputation = u.Reputation - 1

FROM dbo.Users u

INNER JOIN INSERTED i ON u.Id = i.Id

WHERE i.Reputation > 0

AND i.DownVotes > 0

AND i.DownVotes % 5 = 0;

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.