RE: Writing the Correct Query is Important

SSC Guru

Points: 287024

January 16, 2017 at 9:41 am

#1923018

Eric M Russell - Monday, January 16, 2017 8:34 AM
SQL suffers from the same predicament as HTML and JavaScript; it's an accessible high-level language that's widely used by a lot of people, but only a few people actually know how to code it at a professional level. When I'm called in to help resolve a performance issue, it's typical for IT management and application developers to have this prejudicial assumption that the root cause has something to do with the configuration of the database server or network itself. The developers bristle when I suggest that there is something wrong with how the SQL itself is coded. After all, they self evaluate themselves as an 8 out of 10 when it comes to proficiency at SQL. At least initially, they dismiss my advice because they think I'm wasting time by nitpicking their coding style, but when they see how much impact in performance can be achieved simply by modifying a join or removing a user defined function from the where clause, they realize there is so much more to be known.

Eric, you said a mouthful. There are a lot of coders, but not a lot of people who understand how things work. I'm not a consultant, but am asked to resolve performance problems internally. Too many times I hear "you're right that the structure needs work, but we have a lot of things built around it so we can't change it" as the reason for not making real change. The code is most often the source of the problem (the scalar function in the WHERE clause like you said or an implicit cast on a join) but the underlying design is sometimes the limit on making huge improvements.

I also encounter the "hey, it works" syndrome where people get defensive about their code and don't believe it can be made any faster. When it is made 30x faster, they think there's a trick somewhere. Naturally, it couldn't be their cursor. Sadly, I know I'll encounter the same code and the same attitude again soon.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions