I have just been working on a new project and doing things the same way I have for years now. However for the first time, I am constantly thinking about scalability and performance of both the application code and the database.

So in times past, I have been more than happy to insert a new row, then grab the last_insert_id and then translate this into an object by calling the getObjectById(id) method from within whichever persistence framework is in use. I find this is good practice because it will ensure that you are returning exactly what is stored in the database and any error will be picked up there and then, rather than next time you ask for the object.

The other way, since you have all the data required to build the object right in front of you, is to simply create the new object from the supplied data and simply set the objectId once the statement is verified as complete. I have always felt as though there are still some unknowns which can occur, and if there are any triggers in the database which transform the data in any way, then you will have a mismatch in state which is bad.

However, thinking purely about performance, I am obviously taking a hit by running multiple queries to create the object (insert plus select) which adds overhead and I would like to make the application both as fast and secure as possible.

My question is, what would be the most appropriate approach for performance, scalability, security and then for the trade-off between the 3?

  • 3
    recommended reading: Why is asking a question on “best practice” a bad thing?
    – gnat
    May 8 '14 at 4:03
  • @gnat sure, I understand. I realise 'best practice' does not fit 100% of cases, hence best practice not golden rule :), however I think it is necessary to understand what and why best practice is in order to know why you are or are not following it. So it is still better to establish best practice than not. I was also under the impression that this site was more interested in programming concepts than stack overflow.
    – samazi
    May 8 '14 at 4:17
  • it is indeed... and this is why problems underlying "best practice" cliches are perceived with more pain here than at SO
    – gnat
    May 8 '14 at 5:34
  • 1
    The performance hit of extra queries in an n+1 scenario is significant, but a single extra query on the rare path of actually writing new data? If that single extra query impacts your performance, you have bigger problems.
    – Phoshi
    May 8 '14 at 8:36

You have a performance problem when your system does not meet its functional requirements.

So unless your users are complaining or you fail to meet a Service Level Agreement you do not have a performance problem.

In your case its very unlikely that the extra "insert"/"select" would involve an overhead which would be noticeable to an end user, or measurable in terms of the overall system performance.

After all you will probably insert the data at some point anyway, and invoking a select on an object you inserted less than a millisecond ago will not involve any database I/O. In fact you will not get any delays due to IO activity until you "COMMIT" your work. Inserting earlier will give the database a chance to log your change before you commit so your process might actually be faster than the alternative.

"If it ain't broke don't fix it "


First, @JamesAnderson's answer is pointing out to really good points. I also think that you're unnecessarily paranoid about the performance at this point.

Talking about your question, I usually do it differently. Instead of inserting the data into the database and then loading it back again and creating an instance of a class base on that data, I usually create the instance of the class first, and then I use a method like $obj->save() to insert the data into the database; but doing so is really depends on how Robust you have designed your Database and Classes.

For example:

  • Have you implemented a proper encapsulation? Specially a solid setter for fields that should be stored in the database? Or are you exposing any important class properties via public accessor in which they could be modified out of control?
  • Does your database tables accept NULL values when it's not necessary, instead of failing the operation? Or did you set up some Default Values for some fields that might not be really wise and possibly problematic in the future?

The above list could goes much longer, but the point is that if you could trust the data that you have within your objects, and also if you could trust your database that it will insert the correct data or it will fail in any other cases -- then no InsertID will be returned that is a sign of failure; If you meet these points in your design and implementation, then you won't need to insert the data and then load it again to make sure about the integrity of data.

  • Great points. I have also done similar things in the past to encapsulate the the object as a DAOEntity and allow it to take care of its own persistence. I have solid encapsulation, NO public fields and the database design only uses NULLS and default fields where appropriate. In this case the data is coming in via a REST API, so I find it more efficient to simply pass the data through as I only need to return a response header and a resource location for POST (create a new resource) requests. I was just curious what others thought about this idea. Thanks for your answer.
    – samazi
    May 8 '14 at 12:35
  • @SandorA You're very welcome.
    – Mahdi
    May 8 '14 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.