Monin - Peter Kruis presenting on SQL Saturday Sweden 2024

SQL Bad Practices Caught in the Wild – Part 1

As many of you know, at Monin, we’re committed to helping our customers keep their database environments stable and fast. Recently, one of our colleagues had the chance to speak at Data Saturday Sweden (Data Saturday Sweden) about some of the common culprits we encounter during our consultations.

The “Bad Practice” Story

 

In his session, “SQL Bad Practices Caught in the Wild” (SQL Bad Practices Caught in the Wild), he focused on how misunderstandings of SQL Server can cause performance issues—not hardware or configuration problems, but bad code practices that lead to slow queries and system inefficiencies. One example that stood out was a story from a production plant.

It was just another typical day at the factory. Machines whirred steadily, workers moved efficiently, and labels were rolling off the printers, carrying critical details like batch numbers and production dates. Everything seemed to be running like clockwork—until it wasn’t.

Suddenly, the production line stuttered. Labels weren’t printing fast enough. At first, the delay seemed minor—a few seconds longer here, a slight slowdown there—but it soon became impossible to ignore. To make matters worse, the issue would disappear just as suddenly as it started, only to return at random.

The managers were understandably anxious. Every minute of delay was costly, and in a factory, time is as valuable as the products themselves. What was going wrong?

The IT team was brought in to investigate, and they quickly traced the issue to a query responsible for retrieving data to be printed on the labels. Sometimes, the query took over 30 seconds to run, causing timeouts in the label-printing software.

When digging into the problem, we found that the query was pulling data from a view. Nothing unusual about that—until we saw one glaring issue: SELECT *. The query was pulling all columns from the view, even though it only needed five to print the labels.

To make matters worse, the view itself was massive. It pulled data from 40 different tables, totaling 140 columns, and was filled with scalar functions, implicit casting, functions in the WHERE clause, and countless LEFT joins.

And yet, for the label in question, only five columns were required.

The Fix

The solution was simple: stop using SELECT * and only retrieve the data actually needed for the label. This immediately reduced the query’s vulnerability to parameter sniffing and drastically cut down on compilation time. The view, packed with unnecessary LEFT joins, became far more efficient since the query optimizer could now skip unrelated data that was no longer needed.

As we dug deeper, we found another issue: scalar functions within the view were causing the query to run serially, even though large tables were being read. Despite having indexes on the columns being searched, SQL Server wasn’t able to use them because of functions in the WHERE clause. These functions forced SQL Server to calculate the function’s outcome for every single row, which made it impossible to leverage the indexes efficiently.

Additionally, we found a data type mismatch in the query. One column was being compared to a parameter of a different data type, which forced SQL Server to convert the entire column during each comparison. This implicit casting further downgraded performance and made index usage ineffective.

Back to Full Speed

Once we deployed these changes, the production line was back to full speed. Labels printed without delay, and the factory returned to its usual rhythm. The managers breathed a sigh of relief. What had nearly halted the entire production line was solved with a few well-placed optimizations.

Key Takeaway

The takeaway from this story—and from the session—is simple but crucial: SQL is not just about writing code—it’s about writing efficient code. When every second counts, efficiency is everything.

Database Performance Monitoring tools like DBmarlin can assist in detecting inefficient code on your development environment, proactively avoing performance drain on your production database. Next to that, the right database expertise will also do the magic.

 

If you need help optimizing your SQL environment, refining your queries, implement Database Performance Monitoring (using DBmarlin) or guiding your developers to write efficient code, don’t hesitate to contact us.