Asking for your opinion on DB Persistance Frameworks

perrohunter

I'd like to know what do you think about Object-Relational Mapping Frameworks, I'm about to start a project but I'm not sure if i'll sacrifice performance with these.

What are their Pro's and Con's, if you can share a but of your experiences with them I'll really appreciate it

Thanks !

Piranha

Performance of frameworks is not important, it is easily boosted with better hardware. And hardware is really cheap.

The important thing is how much development time it saves, both for the new script and changes when it is finished.

tomhath

I think of frameworks as taking one of two basic approaches to solving the problem of moving data between the application and database; which you want is up to you. We use Java at work so I'll use two of the more popular frameworks as examples of each type:

1) (Hibernate style) assumes the software's object model is the data model, treats the database as nothing more than a repository for storing (or persisting) the objects. When used this way the programmers don't need to know any SQL. Drawback is that the database is specific to the object model and becomes difficult to use for other applications where the mapping from software to tables isn't as good a match. There are tools in Hibernate to manipulate relational data, and they even provide a SQL-like language called HQL, but at that point I think it's easier to just wrote SQL. http://en.wikipedia.org/wiki/Hibernate_(Java)

2) (iBatis style) assumes the programmers know how use SQL and the RDBMS to manipulate sets of data. The framework eases moving the data between the database and software, it doesn't try to be much more than an abstraction layer. http://ibatis.apache.org/overview.html

Both types of frameworks try to help simplify transactions and caching. That can sometimes help, sometimes hurt performance. Personally I prefer learning how to use the DBMS and using the second type of framework. But if you're writing a new application (no legacy database) and you have a bunch of programmers who don't understand database programming the first might be a better choice.

Piranha

perrohunter, I think you should look a bit in this thread, there are some talk about frameworks in there.

tomhath, I work in Java as well, and normally uses Hibernate and HQL. I would like to say that the best way to code is to use both Hibernate and JDBC. That combines the best of automatic handling of databases and the ability to write your own SQL statements when you want that. Example: Showing a list of something is easier with HQL, updating the stock is easier with JDBC.

Sxooter

Piranha;10897952 wrote:
Performance of frameworks is not important, it is easily boosted with better hardware. And hardware is really cheap.

The important thing is how much development time it saves, both for the new script and changes when it is finished.

I'm not sure I entirely agree here. It's very dependent on what kind of application you're making as to whether or not hardware is cheap. If I need a pair of $50,000 servers to handle the load of a large site without the framework, and I need a pair of $250,000 servers to handle the load with the framework, then hardware is not so cheap.

Admittedly, those situations are rare, but they do occur, and when they do, an efficient framework is essential.

Last job I had we were running about 600 requests per second, all the time, and someone made a change in how the framework was accessing the database and we went from 200 ms to run a query to 2.5 seconds. The results, if the change had gotten to production, would have been disasterous. Luckily we caught it in testing.

It's a balance. No one wants to hamstring a 40 man development team by torturing them with lots of SQL, but they need a framework that's efficient enough that they don't have to worry about killing the db server with what seems like a simple query. Being able to step outside the framework, like hibernate allows, is essential for certain tasks which the framework can't handle efficiently.

We also had an update process at the last job that ran in pure ORM mode and took 15 hours to run. The dbas took a look at it, wrote one query, and it ran in about 10 minutes. If we hadn't had the ability to throw regular SQL at the db through hibernate we'd have been stuck with a 15 hour update process that hogged a LOT of db bandwidth and middle tier CPU.

Piranha

Sxooter;10898518 wrote:
I'm not sure I entirely agree here. It's very dependent on what kind of application you're making as to whether or not hardware is cheap.

Of course there is no way to just say "hardware is cheap, don't care about performance". Or "hardware is expensive, performance of the application is everything".

I would still say that in most cases the performance of the framework is not important, the important thing is how you use the framework. A bad example of using the framework is that I once used Hibernate and set all relationships to eager, with the result that all the rows of all the tables in the database was loaded into memory with every select question. The framework (Hibernate) didn't cause a performance issue, my usage of it did.

There are always exceptions, but if you don't use the framework in bad ways those exceptions normally only occur in larger applications or high load applications.