Best practice for object relation mapping to execute delete/update

Question

In doctrine (or any ORM in general), the easiest way to execute a delete/update operation is to select such entity, then remove entity or update single attribute in the code, ended by persist the changes to database.

This is the native way to execute delete/update operation in most of the ORM frameworks. It is very handy since it get rids of the SQL statement. However, by comparing to QueryBuilder/SQL language, it would mean two SQL statements here, first is a SELECT sql, then following by a DELETE/UPDATE sql.

My question here this, for the purpose of convenience and ease of maintenance, is it worth to follow ORM pattern and ignore the performance trade off?

Answer 1

My question here this, for the purpose of convenience and ease of maintenance, is it worth to follow ORM pattern and ignore the performance trade off?

It depends, mainly on the following things:

1. does your application suffer from measureable performance issues?

2. does your ORM provide further optimization features?

3. do you use the ORM just for avoiding the need of writing SQL manually, or do you use it to decouple your application from the specific DB vendor (and it's SQL dialect)?

In case the answer to "1." is "no", stop overthinking this and stick to your ORM. Optimizing "just in case" leads to overengineering and is known as "premature optimization".

If the answer to "1" is yes, "2" should be always worth a check, make sure you understood the capabilities of your ORM well and tried what is possible. Try googling for " performance optimization", and you will get tons of results for every popular ORM.

If "2" does not help, and coupling to a specific SQL dialect is not a huge problem for you, at least not in some restricted areas of the code, then go ahead and write your queries manually where it helps you to reach the performance requirements.

To be honest, ORM technology is still subject of controversial debates about if it is really useful, or if it is an anti pattern. You will find lots of articles in the Web or posts on Stackoverflow (like this 9 year old one) discussing the pros and cons of ORMs versus "native SQL programming". Lots of the arguments against ORMs are founded on the observations you already made, that it is often easier to optimize queries manually than to configure an ORM to produce optimized queries.

Answer 2

You should be hiding your ORM behind a repository.

This allows you to optimise things like a bulk update to custom SQL where required.