DaveWentzel.com            All Things Data

Object Relational Mappers Series - The SELECT * Problem

DBAs have been bitching for years that we should never use SELECT * in our queries (or INSERT INTO statements without a column list).  ORMs, usually by default, violate this principle.  

We've all worked with VERY wide tables where we just stuff data that we can't get to fit logically into any other table.  Nobody likes it, but it happens.  These tables will have LOTS of foreign keys.  In most cases only a handful of columns will be applicable to any single key that we are querying, so we should really be optimizing so that we are only returning those handful of columns, and nothing else, when needed.  

ORMs, however, usually want to do a SELECT *.  This means that your DBA, who properly partitioned the table and added the necessary covering indexes, did all of his work for nothing.  ORMs want to do this for a number of reasons.  Future extensibility (I can add more columns without revisiting the business objects) and reusability (the ORM shields you from a lot of smaller sub-business objects that you may want to create against that WIDE table) are the two main reasons.  

IdeaBlade, for instance, has a ColumnSelectionOptions option that is set to Select All Columns by default.  You can also set it to Allow Column Selection.  This may not be a huge drawback to using ORMs, but again, the abstraction is coming at a cost.