Possible Duplicate:
Using an ORM or plain SQL?
Would you elect to use an ORM or some kind of home spun DAL? And why?
The advantages of an ORM seem obvious - better structure / organization, better language fit etc. But I worry about performance issues. Anyone have war stories to share? Any insights on not so obvious risks or rewards would be much appreciated.
I'll just leave this as my personal opinion, as this is a bit of a holy topic.
Everytime I start a project and try out a ORM (Be it L2SQL, Subsonic, EF NHibernate etc), I always end up scrapping it as some point and rewriting the DAL by hand using sprocs.
ORM's are handy for quick scaffolding, but if you design your entity layer correctly, plugging in a new data access layer is pretty simple, and I run into too many issues where I'm either fighting the ORM to do what I want, or the ORM is just plain doing something dumb (N+1 queries, or pulling down related tables when I really only need a primary key lookup etc).
I'd use an ORM for exactly the reasons you mention.
They typically perform well. If there are performance issues in a certain area, you can always optimize that portion later, or switch to straight SQL if it truly helped, as the two technologies are not mutually exclusive.
I would (and do) elect to use an ORM, and learn how it works well enough to squeeze as much performance out of it as possible.
For example; most ORMs let you use a lot of Stored Procedures/server functions so you can have highly-optimized code running in the best place possible. But you have to know how/when to do that.
There are also techniques you can use that go below the surface of the ORM. For example, Compiling LINQ to SQL queries.
I think a lot of the times that rolling ones own DAL can end up not any better performance-wise, just because one is trying to re-create a lot of boilerplate code.
I'd say use an ORM. I was a little opposed to them at first because writing straight SQL is pretty easy for simple projects, I didn't see the added benefit. But as your project grows, the queries get more complicated. Especially for things like hierarchies... if your ORM supports those kinds of things, it really saves a lot of time.
If you're worried about performance issues, wait until it starts to become a problem, then inspect the SQL your ORM is generated and use it's raw_sql functions to rewrite it more efficiently, or use the other auxiliary methods to coax it in the right direction.
It depends really much of the application and how much is it going to scale, also if you are worry about performance then you have the same dilema with Programming language, IMHO ORM are really great for reaseon you already said but they (as everything) have bounderies, most of them of performance and you eventually will have to deal with Query wethr you call them SQL or HQL or ANY_OTHER_SUTFF_QL,
My advice if you choose ORM then try to exploit all its capabilities many projects end up using ORM and just SAVE UPDATE DELETE without using Hierarchy Pattern or Cache Improvements
I'd be surprised if any business requirements were satisfied by either choice. I also doubt that there a lot a defects that can be directly attributed to either choice.
Personally I find it pretty easy to get data into and out of data sources, and its usually not were I spend a lot of time, but that might just be me.
I'd say that unless you had a strong preference for one or the other it probably won't matter.
I think it depends on what kinda of project your are working. If you are programming using some OO language then it may be a good idea to use ORM. But if your project is more low-level, or you are really worried about performance (I believe a good designed ORM wont give you bad performance) then straight sql may be the best choice.