The issue here is with the ORM. Active record pattern is fine when objects exist in isolation but this is rarely the case. Imagine a case where you need to add a passenger to a flight manifest. The passenger:manifest is a many:many relationship. I.e. the manifest has many passengers and a passenger can be on many manifests so you have a bridging table ManifestPassenger with mp_id, manifest_id and passenger_id. You may or may not need to create the passenger record and retrieve the new passenger_id and then create the MP record. As these are separate database activities but are related to the same business event, good practice dictates that either both succeed or both fail so they need to occur within the same database Transaction scope which needs additional code in your middleware to start and either commit or rollback. Alternatively you could call a stored procedure in your database and thus will handle the transaction scoping for you as well as drastically reducing network traffic. ORMs are also notoriously poor at optimising complex queries. If you need to get the contact details for everyone on this flight, plus the contact details of every passenger they have shared a flight with in the last 7 days (Covid track and trace), you can do that in the database as a single query and the internally managed database stats will give you a data access strategy that is pretty close to optimal in most cases. The ORM may need literally 1000s of dB requests to achieve the same data set. Yes, I'm a SQL guy...
Hi Aaron, thank you for your detailed comment. I believe that massive import of multiple datasets with many to many relation it's not a use case of the "insert on duplicate key" clause.
Of course "transactions" are the way to go to run multiple statements safety, and it's what we use and recommend on our articles just as result of our experience. You can read more about other approaches we use to deal with database here: inspector.dev/make-your-applicatio...
I used an ORM to write code examples for two reasons:
It's the most common interface to the database for the majority of software developers;
Show to interested developers that ORM, Query builder, and any other tools are just tools. The most important thing is the strategy and the goal you want to reach.
We had to spend many hours searching online and asking other developers to find out that MySQL offered this simple feature. I hope other developers can find our experience helpful and share this tips with other colleagues since our customers are typically very sensitive to the problem of performance.
Inspector is an application monitoring tool, we are very focused on application performance, that's why my articles go around this kind of issues.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
The issue here is with the ORM. Active record pattern is fine when objects exist in isolation but this is rarely the case. Imagine a case where you need to add a passenger to a flight manifest. The passenger:manifest is a many:many relationship. I.e. the manifest has many passengers and a passenger can be on many manifests so you have a bridging table ManifestPassenger with mp_id, manifest_id and passenger_id. You may or may not need to create the passenger record and retrieve the new passenger_id and then create the MP record. As these are separate database activities but are related to the same business event, good practice dictates that either both succeed or both fail so they need to occur within the same database Transaction scope which needs additional code in your middleware to start and either commit or rollback. Alternatively you could call a stored procedure in your database and thus will handle the transaction scoping for you as well as drastically reducing network traffic. ORMs are also notoriously poor at optimising complex queries. If you need to get the contact details for everyone on this flight, plus the contact details of every passenger they have shared a flight with in the last 7 days (Covid track and trace), you can do that in the database as a single query and the internally managed database stats will give you a data access strategy that is pretty close to optimal in most cases. The ORM may need literally 1000s of dB requests to achieve the same data set. Yes, I'm a SQL guy...
Hi Aaron, thank you for your detailed comment. I believe that massive import of multiple datasets with many to many relation it's not a use case of the "insert on duplicate key" clause.
Of course "transactions" are the way to go to run multiple statements safety, and it's what we use and recommend on our articles just as result of our experience. You can read more about other approaches we use to deal with database here: inspector.dev/make-your-applicatio...
I used an ORM to write code examples for two reasons:
We had to spend many hours searching online and asking other developers to find out that MySQL offered this simple feature. I hope other developers can find our experience helpful and share this tips with other colleagues since our customers are typically very sensitive to the problem of performance.
Inspector is an application monitoring tool, we are very focused on application performance, that's why my articles go around this kind of issues.