Bad Standards: #2--Too Many Columns!
Database application performance can be impacted by many factors - but the number one cause of poor relational performance is usually poorly coded SQL. Sometimes, the mistakes are simple to correct - and that is the case with this week's "bad standard."
One of the simplest mistakes made by many SQL programmers is including too many columns in the SELECT-list of their SQL statements. The only columns that should be included in the SELECT-list are those that are needed to meet the business requirements of the query. Sometimest this is simplified to "avoid SELECT *". That is actually a good standard (excepting, of course, quick & dirty ad hoc queries), but it does not capture the true requirement, that being to reference only exactly what is required and nothing more.
The bad standard may read something like this: Every column referenced in a WHERE clause of your SQL statement(s) should also be included in the SELECT-list of that statement.
That is an extremely nasty standard. Some shops do it to support their development tools or to standardize (for some strange reason). But all that it does is build performance degradation into your applications. Consider the following statement:
SELECT FIRSTNAME, LASTNAME, EMPNO
What is wrong with this statement? Well, there is no reason for EMPNO to be in the SELECT-list. We know its value will always be 700 because of the WHERE clause. Now this is a small issue, right? Maybe. What if this statement runs thousands of times a day? Every column that the DBMS must pick up and return to the application requires additional resources - a small amount of resources, but additional resources none-the-less. If we remove the column from the SELECT-list we remove the requirement to use those resources. Now multiple that by the thousands of times the statement runs and we're saving something!
The bottom line is that this standard at best has no viable reason to exist - and at worst can cause a performance problem. If it is in your company's standards manaual, snip it right out today.
© 2005, Mullins Consulting, Inc.
FROM EMPLOYEE
WHERE EMPNO = '700';
