Recently, I have noticed that there are numerous developers who struggle to comprehend how SQL functions when even the slightest modification is made to it.
I collaborated with a Developer who provided me with a SQL script consisting of over 1800 lines and multiple joins. My team of DBAs and I dedicated nearly 3 days to fine-tune the query, resulting in a significant improvement from 2 minutes to just 20 milliseconds per execution.
However, a week later, the same developer returned claiming that the SQL performance had deteriorated once again. Upon testing it with the optimized code I had, the results remained consistent. Nevertheless, the developer insisted that it was not functioning as expected.
Upon further investigation during a call, we discovered that they were using a slightly altered query, although it appeared nearly identical.
When questioned, the Developer mentioned, "We simply added 3 columns to the script after it was optimized. Why would that make a difference? They are additional columns from the same table," expressing confusion.
I had to clarify that even the smallest alteration, whether it involves adding a new column from existing tables or modifying a condition, will impact the entire SQL plan.
Although it was challenging for the developer to accept, it is crucial for them to understand this fundamental concept.
