In Delphi should I use multiple database inserts from within a loop or use a stored procedure?

Question

I'm looking for the best way to handle this situation. I want to store an amortization schedule inside a databse table. Each row contains the date, current balance, payment, pricipal, interest, and new balance. For a typical 30 year mortgage this would be 360 rows or database inserts.

Should I do the calculations inside a loop using Delphi and do an insert for each result or should I perform these calculations inside a stored procedure?

This would be a single user, local machine, desktop application.

Answer 1

Prepared queries and stored procedures are comparable performance-wise. As an application developer I detest stored procedures with a passion because they move logic from inside the application, where I can find it, to somewhere else not visible when looking through the source code. And let's face it, nobody is going to redevelop an application in a different language if it works.

So, if your thing is databases and SQL and you are comfortable with that, then stored procedures are fine. However, if you are primarily an application developer, I cannot see any benefit of using stored procedures over having the queries executed from code.

Answer 2

I would do the operations in the stored procedure. That way working with data is in the database where it belongs.

Also, by keeping all data related operations in the database you save yourself the hassle of coding it again if sometime in the future you choose to switch language.