Should I be worried that ORMs return all columns by default?

Question

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

Of course, I know there are workarounds:

• Create an SQL View and Create Models and Mappings in a View
• Use the stored procedure and create models and mappings in the returned result set

I know that following certain practices can help mitigate this:

• Ensuring the sufficiency of rows when selecting data
• Ensuring your tables are not overly wide (large number of columns and / or large data types)

So here are my questions:

• Are the above practices sufficient or do I still need to find ways to limit the number of columns returned?

• Are there other ways to restrict return columns other than those listed above?

• How do you usually approach this in your projects?

Thanks in advance.

UPDATE: This is because SELECT * is considered bad practice. See this discussion .

Answer 1

One of the reasons for using ORM of almost any type is to postpone many lower-level problems and focus on business logic. As long as you keep your joins reasonable and your tables become intelligent, ORMs are designed to facilitate data input and output, and this requires all the available row.

Personally, I consider problems such as this premature optimization, until I come across a specific case that is afraid down because of the width of the table.

Answer 2

Firstly: a great question, and someone asked about this time! :-)

Yes, the fact that ORM usually returns all columns for a database table is something you need to consider when designing your systems. But, as you already mentioned, there are ways around this.

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

This is not a problem when you really need the whole object and all its properties, and as long as you load a few lines, this is also great - convenience exceeds raw performance.

You may need to think a little about changing database structures: for example,

• perhaps place large columns, such as BLOBs, in separate tables with a 1: 1 link to your base table. So choosing in parent tables doesn't capture all these big drops of data

• it is possible to put groups of columns, which are optional, that can only be displayed in certain situations, into separate tables and link them - again, to preserve the lean'n'mean base tables

Also: Avoid trying to “arm” your ORM to perform massive operations - this is simply not their forte.

And: keep track of performance and try to choose an ORM that allows you to modify certain operations, for example. stored procedures - this allows Entity Framework 4. Therefore, if the hits kill you - perhaps you just write the stored procedure Delete for this table and process this operation in different ways.

Answer 3

The question here describes your options pretty well. You are mostly limited to manual HQL / SQL processing. This is what you want to do if you run into scalability issues, but if in my experience it can have a very big positive effect. In particular, it saves a lot of disk and network I / O, so your scalability can move a lot. Not what you need to do right away: analyze, then optimize.

Answer 4

Are there other ways to restrict returned columns other than those listed above?

NHibernate allows you to add predictions to your queries, so you won’t need to use views or procs just to limit your columns.

Answer 5

For me, this was a problem only if the tables had LOTS columns> 30 or if the column had a lot of data, for example, more than 5000 characters in a field.

The approach I used was just to map another object to an existing table, but only to the fields that I need. So for a search that fills a table with 100 rows, I would have MyObjectLite, but when I click to view the details of that row, I would call GetById and return a MyObject with all the columns.

Another approach is to use custom SQL, Stroed procs, but I think you should go this way if you really need a profit and users complain. SO, if there is no performance problem, do not waste time fixing a problem that does not exist.

Answer 6

You can limit the number of columns returned using Projection and Transformers.AliasToBean and DTO, as it looks in the criteria API:

 .SetProjection(Projections.ProjectionList() .Add(Projections.Property("Id"), "Id") .Add(Projections.Property("PackageName"), "Caption")) .SetResultTransformer(Transformers.AliasToBean(typeof(PackageNameDTO))); 

Answer 7

In LLBLGen Pro, you can return Typed Lists , which not only allows you to determine which fields are returned, but also allows you to join data, so you can pull your own list of fields from several tables.

In general, I agree that for most situations this is a premature optimization.

One of the big advantages of using LLBLGen and other ORMs (I’m just confidently talking about LLBLGen because I have used it since its inception) is that the data access performance has been optimized by people who understand the problems better than your average bear.

Whenever they figure out a way to speed up their code further, you get these changes “for free,” simply by re-creating your data layer or installing a new dll.

If you don't consider yourself an expert when writing data access codes, ORMs are likely to improve the efficiency and accuracy of most developers.