Should I be concerned that ORMs, by default, return all columns?

Question

In my limited experience in working with ORMs (so far LLBL Gen Pro and Entity Framework 4), I've noticed that inherently, queries return data for all columns. I know NHibernate is another popular ORM, and I'm not sure that this applies with it or not, but I would assume it does.

Of course, I know there are workarounds:

• Create a SQL view and create models and mappings on the view
• Use a stored procedure and create models and mappings on the result set returned

I know that adhering to certain practices can help mitigate this:

• Ensuring your row counts are reasonably limited when selecting data
• Ensuring your tables aren't excessively wide (large number of columns and/or large data types)

So here are my questions:

1. Are the above practices sufficient, or should I still consider finding ways to limit the number of columns returned?

2. Are there other ways to limit returned columns other than the ones I listed above?

3. How do you typically approach this in your projects?

Thanks in advance.

UPDATE: This sort of stems from the notion that SELECT * is thought of as a bad practice. See this discussion.

Answer 1

One of the reasons to use an ORM of nearly any kind is to delay a lot of those lower-level concerns and focus on the business logic. As long as you keep your joins reasonable and your table widths sane, ORMs are designed to make it easy to get data in and out, and that requires having the entire row available.

Personally, I consider issues like this premature optimization until encountering a specific case that bogs down because of table width.

Answer 2

First of : great question, and about time someone asked this! :-)

Yes, the fact an ORM typically returns all columns for a database table is something you need to take into consideration when designing your systems. But as you've mentioned - there are ways around this.

The main fact for me is to be aware that this is what happens - either a SELECT * FROM dbo.YourTable, or (better) a SELECT (list of all columns) FROM dbo.YourTable.

This is not a problem when you really want the whole object and all its properties, and as long as you load a few rows, that's fine, too - the convenience beats the raw performance.

You might need to think about changing your database structures a little bit - things like:

• maybe put large columns like BLOBs into separate tables with a 1:1 link to your base table - that way, a select on the parent tables doesn't grab all those large blobs of data

• maybe put groups of columns that are optional, that might only show up in certain situations, into separate tables and link them - again, just to keep the base tables lean'n'mean

Also: avoid trying to "arm-wrestle" your ORM into doing bulk operations - that's just not their strong point.

And: keep an eye on performance, and try to pick an ORM that allows you to change certain operations into e.g. stored procedures - Entity Framework 4 allows this. So if the deletes are killing you - maybe you just write a Delete stored proc for that table and handle that operation differently.