VND - Vu Nguyen Learn On The Go

Conventional DAL

ORM

Disadvantages of the Conventional Model

1. The stored procedures or SQL commands need to be written well in advance.
2. Does not support multiple databases – The stored procedures written are only for the SQL server.  The same has to be done for other databases as well.
   Writing SQL queries is a laborious task as well as error prone.
3. Most developers do not like writing SQL queries as they do not want to get out of Object oriented world.
4. Queries involving even the smallest business logic need to be changed in case of change in the business rules.

1. Automatic generation of SQL statements – The ORM layer generates automatically SQL statements.  This helps the programmers concentrate more on the actual business logic removing the overhead of the SQL statements.
2. Support of the Multiple Databases – Majority of the ORM packages available in the market support ANSI SQL standards.  Hence, they can be used with most of the RDBMS.  This can very useful in product development.
3. ORM emphasizes on fully following the object oriented approach.  Every thing is an object. Database CRUD operations are typically called as a function.  We will have functions on the business objects like Save, Update and Delete.

Disadvantages

1. Lack of standards – We have variety of ORM packages available in the market.  We do not have common standard or conventions followed.  Lack of such standard has made the programmers job tough.  An ORM package may be completely different from another one.
2. Performance related issues - Most of ORM related packages use reflection.  As we know, using   Reflection excessively is a performance overhead.  (But there are some ORM packages which do not use the same.)

Conventional Model

ORM Model

Programmer needs to write all the SQL queries for each and every interaction with the database.

Programmer needs not write even a single SQL query for any interaction with the database.

It is tough to handle the changes to the SQL queries in case of any change requests.  This is in comparison with ORM.

The change requests to any database related activities can be done in a very easy way.  As these are classes and methods we can keep the track of the changes with version control systems pretty accurately.

We generally have a separate team for handling DDL (Data Definition Language) and DML (Data Manipulation Language) issues.

We may have a team for the same here, but their work will be limited to only DDL. As we do not have DML in form of queries, programmers are responsible for the DML related tasks.  Hence, programmer has a better control on the same.

Support for Transactions is available.

It is a common notion that Transactions are not available in ORM. But Support for the Transactions are very much available.

Chance of Errors due to the SQL queries. This is more as the complexity of the SQL queries.

Chance of Errors is less as we have our code written in classes and methods.

We have some Interfaces to the database to see the execution of the SQL statements like Query Analyzer.  This helps the programmer for testing the database.

In most of the ORM packages we do not have such a kind of facility.  Some of the ORM packages have their own language called as OQL – Object query Language.