ORM or SQL in large, scalable and MAINTAINABLE web application?

Question

I know there already are a lot of posts floating on the web regarding this topic.

However, many people tend to focus on different things when talking about it. My main goal is to create a scalable web application that is easy to maintain. Speed to develop and maintain is far more appreciated BY ME than raw performance (or i could have used Java instead).

This is because i have noticed that when a project grows in code size, you must have maintainable code. When I first wrote my application in the procedural way, and without any framework it became a nightmare only after 1 month. I was totally lost in the jungle of spaghetti code lines. I didn't have any structure at all, even though i fought so badly to implement one.

Then I realized that I have to have structure and code the right way. I started to use CodeIgniter. That really gave me structure and maintainable code. A lot of users say that frameworks are slowing things down, but I think they missed the picture. The code must be maintainable and easy to understand.

Framework + OOP + MVC made my web application so structured so that adding features was not a problem anymore.

When i create a model, I tend to think that it is representing a data object. Maybe a form or even a table/database. So I thought about ORM (doctrine). Maybe this would be yet another great implementation into my web application giving it more structure so I could focus on the features and not repeating myself.

However, I have never used any ORM before and I have only learned the basics of it, why it's good to use and so on.

So now Im asking all of you guys that just like me are striving for maintainable code and know how important that is, is ORM (doctrine) a must have for maintainable code just like framework+mvc+oop?

I want more life experience advices than "raw sql is faster" advices, cause if i would only care about raw performance, i should have dropped framework+mvc+oop in the first place and kept living in a coding nightmare.

It feels like it fits so good into a MVC framework where the models are the tables.

Right now i've got like 150 sql queries in one file doing easy things like getting a entry by id, getting entry by name, getting entry by email, getting entry by X and so on. i thought that ORM could reduce these lines, or else im pretty sure that this will grow to 1000 sql lines in the future. And if i change in one column, i have to change all of them! what a nightmare again just thinking about it. And maybe this could also give me nice models that fits to the MVC pattern.

Is ORM the right way to go for structure and maintainable code?

Answer 1

A lot of users say that frameworks are slowing things down, but I think they missed the big picture. The code MUST BE MAINTAINABLE and EASY TO UNDERSTAND.

A well-structured, highly-maintainable system is worthless if its performance is Teh Suck!

Maintability is something which benefits the coders who construct an application. Raw performance benefits the real people who use the app for their work (or whatever). So, whose concerns ought to be paramount: those who build the system or those who pay for it?

I know it's not as simple as that, because the customer will eventually pay for a poorly structured system - perhaps more bugs, certainly more time to fix them, more time to implement enhancements to the application. As is usually the case, everything is a trade-off.

Answer 2

I think you missed the picture. Performance is everyday for your users, they care not at all about maintainability. You are being ethnocentric, you are concerned only for your personal concerns and not those of the the people who pay for the system. It isn;t all about your convenience.

Perhaps you should sit down with the users and watch them use your system for day or two. Thenyou should sit down at a PC that is the same power as the ones they use (not a dev machine) and spend an entire week doing nothing but using your system all day long. Then you might understand their point.

Answer 3

I've started developing like you, without orm tools.

Then i worked for companies where software development was more industrialized, and they all use some kind of orm mapping tool (with more or less features). The development is far easier, faster, produce more maintainable code, etc.

But i've also seen the drawbacks of these tools : very slow performance. But it was mostly misuses of the tool (hibernate in that case).

Orm tool are very complex tool, so it is easy to misuse them, but if you have experience with them, you should be able to get nearly the same performances as with raw sql. I would have three advices for you :

• If performance is not critical, use an orm tool (choose a good one, i am not developing with php, so i can't give you a name)
• Be sure for each feature you add, to check the sql that the orm tool produce and send to the database (thanks to a logging facility for example). Think if it is the way you would have written your queries. Most of the inefficiencies of orm tools come from unwanted data that are gathered from the db, unique request split in multiple ones, etc. Slowness rarely comes from the tool in itself
• Do not use the tool for everything. Choose wisely when not to use it (you reduce maintainability each time you do raw db access), but sometimes, it isn't just worst trying to make the orm tool do something it was not developed for.

Edit: Orm tool are most useful with very complex model : many relationships between entities. Which is most of the time encountered in configuration part of the application, or in complex business part of the application. So it is less useful if you have only few entities, and if there is less chance they get changed (refactored).

The limit between few entities and many is not clear. I would say more that 50 differents Types (sql tables, without join tables) is many, and less than 10 is few.

I don't know what was used to build stackoverflow but it must have been very carefully performance tested before.

If you want to build a web site that will get such a heavy load, and if you don't have experience with that, try to get someone in your team that have already worked on such sites (performance testing with a real set of data and a representative number of concurrent users is not an easy and fast task to implement). Having someone that have experience with it will greatly speed up the process.

Answer 4

Ajsie,

My vote is for an ORM. I use NHibernate. It's not perfect and there is a sizable learning curve. But the code is much more maintainable, much more OOP. Its almost impossible to create an application using OOP without an ORM unless you like a lot of duplicate code. It will definitely eliminate probably the vast majority of your SQL code.

And here's the other thing. If you're are going to build an OOP system, you'll end up writing your own O/R Mapper anyway. You'll need to call dynamic SQL or stored procs, get the data as a reader or dataset, convert that to an object, wire up relationships to other objects, turn object modifications into sql inserts/updates, etc. What you write will be slower and more buggy than NHibernate or something that's been in the market for a long while.

Your only other choice really is to build a very data centric, procedural application. Yes it may perform faster in some areas. I agree that performance IS important. But what matters is that its FAST ENOUGH. If you save a few milliseconds here and there doing procedural code, your users will not notice the performance increase. But you 'll notice the crappy code.

The biggest performance bottle-necks in an ORM are in the right way to pre-fetch and lazy-load objects. This gets into the n-query problems with ORMs. However, these are easily solved. You just have to performance tune your object queries and limit the number of calls to the database, tell it when to use joins, etc. NHibernate also supports a rich caching mechanism so you don't hit the database at all at times.

I also disagree with those that say performance is about users and maintenance is about coders. If your code is not easily maintained, it will be buggy and slow to add features. Your users will care about that.

I wont say every application should have an ORM, but I think most will benefit. Also don't be afraid to use native SQL or stored procedures with an ORM every now and then where necessary. If you have to do batch updates to millions of records or write a very complex report (hopefully against a separate, denormalized reporting database) then straight SQL is the way to go. Use ORMs for the OOP, transactional, business logic and C.R.U.D. stuff, and use SQL for the exceptions and edge cases.

I'd recommend reading Jeffrey Palermo's stuff on NHibernate and Onion Architecture. Also, take his agile boot camp or other classes to learn O/R Mapping, NHibernate and OOP. Thats what we use: NHibernate, MVC, TDD, Dependency Injection.