Re:Database development mistakes made by application developers
by protomyth on Monday December 13, 2010 @10:58AM
(#2000813)
6 and 13 (use IN not OR) really rely on the optimizer doing its job and can hide some nasty performance issues. I do know one release of Sybase would freak on IN but be fine for an OR (have no idea why). Also, views where very dicey.
Number 6 needs to be used with discretion.
When you reference a complex view, you are in essence making use of a stored query snippet -- there is no magic to the view, and unless you've materialized it (which is very rare), there is no efficiency advantage. All it really is is code sharing.
So what's the problem then?
The problem then is that the query analyzer has to assume that you actually care about every element of said view, every returned column, every spurious join. I've dealt with too many performance problems where people made use of an overly generous view that invalidated most indexes, removed the potential for covering indexes (the #1 most important performance element of a database), and did a lot of joins that were irrelevant to the usage.