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
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.
NET | C & C++ Windows Programming |
SQL Server | VBA |
Visual Basic |
Deep Learning with Python by François Chollet(12563)
Hello! Python by Anthony Briggs(9911)
OCA Java SE 8 Programmer I Certification Guide by Mala Gupta(9794)
The Mikado Method by Ola Ellnestam Daniel Brolund(9775)
Dependency Injection in .NET by Mark Seemann(9335)
Algorithms of the Intelligent Web by Haralambos Marmanis;Dmitry Babenko(8292)
Test-Driven iOS Development with Swift 4 by Dominik Hauser(7758)
Grails in Action by Glen Smith Peter Ledbrook(7693)
The Well-Grounded Java Developer by Benjamin J. Evans Martijn Verburg(7557)
Becoming a Dynamics 365 Finance and Supply Chain Solution Architect by Brent Dawson(7017)
Microservices with Go by Alexander Shuiskov(6784)
Practical Design Patterns for Java Developers by Miroslav Wengner(6698)
Test Automation Engineering Handbook by Manikandan Sambamurthy(6637)
Secrets of the JavaScript Ninja by John Resig Bear Bibeault(6409)
Angular Projects - Third Edition by Aristeidis Bampakos(6047)
The Art of Crafting User Stories by The Art of Crafting User Stories(5575)
NetSuite for Consultants - Second Edition by Peter Ries(5508)
Demystifying Cryptography with OpenSSL 3.0 by Alexei Khlebnikov(5310)
Kotlin in Action by Dmitry Jemerov(5061)
