Feed aggregator
CodeSOD: Query Query Query
Bob's employer had a data-driven application which wasn't performing terribly well. They had some in-house database administrators, but their skills were more "keep things running," and less "do deep optimizations". The company opted to hire a contract DBA to come in, address the performance problems, and leave.
In actual fact, the DBA came in, ran some monitoring, and then simply wrote some guidance- generic, and frankly useless guidance. "Index on frequently queried fields," and "ensure database statistics are gathered on the appropriate schedule."
The only meaningful output of the contractor's visit was a list of the badly performing stored procedures. Bob was given the list and told, "Go fix these."
One stored procedure needed to populate variables with data from the order table. Specifically, it needed to collect sender_id, user_group, and file_ref from the orders table. Here's how it did that:
if (@origin = -2 or @origin = -4 or @origin = -5 or @origin = -6 or @origin = -7 or @origin = -8 or @origin = -9) begin select @id = sender_id from order where ref = @mur select @group = user_group from order where ref = @mur if (@ltaId is null) begin select @ltaId = null end select @file_ref = file_ref from order where ref = @mur select @ref = ref from order where mur = @mur end else begin select @id = sender_id from order where mur = @mur select @group = user_group from order where mur = @mur if (@ltaId is null) begin select @ltaId = null end select @file_ref = file_ref from order where mur = @mur select @ref = ref from order where mur = @mur endLet's start with the if condition. We never love magic numbers, but that's hardly a WTF. The real problem is that the two branches differ only in whitespace. There's no reason for the if. Perhaps once upon a time there was, but there isn't now.
Now, all the fields we need are in the table order, they're all selected by the field mur which is a unique key, but for some reason, this code needs to run the query three times to get its data. Also, mur is a unique key in the domain but not in the database- no unique constraint or index was applied, which is part of the reason performance was as bad as it was.
But the real highlight, for me, is how @ltaId gets set. If it's null, set it equal to null. That's a ^chef's kiss^ right there. Beautiful(ly horrible).
[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!US Bans Noncompete Agreements For Nearly All Jobs
Read more of this story at Slashdot.
Generative AI Arrives In the Gene Editing World of CRISPR
Read more of this story at Slashdot.
Try Something New To Stop the Days Whizzing Past, Researchers Suggest
Read more of this story at Slashdot.
Oracle Is Moving Its World Headquarters To Nashville
Read more of this story at Slashdot.
Change Healthcare Finally Admits It Paid Ransomware Hackers
Read more of this story at Slashdot.
What Comes After OLED? Meet QDEL
Read more of this story at Slashdot.
The Ray-Ban Meta Smart Glasses Have Multimodel AI Now
Read more of this story at Slashdot.
HashiCorp Reportedly Being Acquired By IBM
Read more of this story at Slashdot.
Ex-Amazon Exec Claims She Was Asked To Ignore Copyright Law in Race To AI
Read more of this story at Slashdot.
Linux Can Finally Run Your Car's Safety Systems and Driver-Assistance Features
Read more of this story at Slashdot.
iPhone Sales Drop 19% in China
Read more of this story at Slashdot.
AI Is Poisoning Reddit To Promote Products and Game Google With 'Parasite SEO'
Read more of this story at Slashdot.
How GM Tricked Millions of Drivers Into Being Spied On
Read more of this story at Slashdot.
Apple Cuts Vision Pro Shipments As Demand Falls 'Sharply Beyond Expectations'
Read more of this story at Slashdot.
FTC To Vote On Noncompete Ban
Read more of this story at Slashdot.
No One Buys Books Any More
Read more of this story at Slashdot.
Fedora Linux 40 Officially Released
Read more of this story at Slashdot.
China's Ageing Tech Workers Hit By 'Curse of 35'
Read more of this story at Slashdot.
Microsoft Launches Phi-3 Mini, a 3.8B-Parameter Model Rivaling GPT-3.5 Capabilities
Read more of this story at Slashdot.