Thomas LaRock (Blog|Twitter) created a new blog party last month, Meme Monday. For this month’s event, Tom asked us to think of nine things that can go wrong with SQL that are not caused by disk issues. So here’s my list.
- Assuming when writing SQL that the AND and OR conditions of a WHERE clause are evaluated in order, thus allowing you to rely on a “short-circuit” logic. While some languages operate this way, SQL does not. I’ve had developers come to me asking why a query fails because they thought a higher condition in a WHERE clause should have prevented evaluation at a lower condition.
- Similar to above, I’ve heard developers say that JOINS are evaluated in the order they are written. However, unless you use the FORCE ORDER query hint (not advisable unless there is a good reason for doing this), the optimizer might choose a different order.
- Here’s another issue with JOIN and WHERE that I encounter regularly. If you are writing an INNER JOIN, then a join condition can be placed in the ON clause or a WHERE clause and evaluation is the same. However, I tell developers that even though the evaluation is the same, use this convention – always put join conditions in the ON clause and filters in a WHERE clause. The reason is because whether a condition is in the ON or WHERE clause does matter when evaluating, say, a LEFT OUTER JOIN, and you can get different results depending on where the condition is placed. There are two problems I often see happen in this context. The first is when an INNER JOIN is later turned into a LEFT OUTER JOIN and, say, the developer doesn’t notice that filter conditions were placed in the ON clause. The second is when there is a LEFT OUTER JOIN and whether conditions are placed into the ON or WHERE clause doesn’t impact results under test conditions, but once it gets moved to production a more diverse data set is encountered that leads to situations where placement of conditions matters.
- Above I put the word convention in bold because another problem I encounter is lack of naming conventions for database objects. I’m not picky about what is used, but some consistency goes a long way when you are trying to step in and diagnose issues.
- Speaking of stepping in and diagnosing issues, nested views are very nasty. I know they are allowed. And I know that in the mind of whoever created them at the time it was easy to keep them straight. However, it’s not fun to be the person that gets to walk in years later and try to figure out a performance issue when there are views querying views querying…
- Forgetting to account for case-sensitive collation with CASE and IF statements, especially if ELSE is used to handle a defined situation.
- Use of table hints such as READUNCOMMITTED to speed up queries without considering whether or not the consequences (e.g. allowing dirty reads) are acceptable.
- Use of table variables because of the incorrect belief that they are in memory and therefore faster than temp tables.
- Not having database object creation scripts under source control.
- Database server overheats because admins in the data center take the cooling fans out of your server to create a wind tunnel for testing the aerodynamics of their Lego model of the Millenium Falcon.
Okay, that last one has never happened because it would just be silly. Nobody cares about the aerodynamics of the Millenium Falcon because it flies in outer space. Duh.
Notice anything in common about the first nine issues? They’re all related to writing and maintaining SQL code. These are issues that can be explored and addressed without expensive hardware additions, architecture changes, etc.
So, instead of listing tags I’ll just say that if you read this and haven’t been tagged by anyone but want to be, then “tag, you’re it.” Have fun and thanks to Tom for hosting this party as well as the previous one!