CODEWITHSUNDEEP
databasedelphistored-proceduresloops
Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

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

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.

Upvotes: 5

Views: 828

Answers (4)

Leonardo Herrera
Leonardo Herrera

Reputation: 8406

If you happen to use AnyDAC, it supports ArrayDML for all their supported databases. I think this is one nifty feature. This is commercial software, but a very good investment. (I'm not associated to them in any way, except as a very satisfied customer.)

See Very High Performance using the Array DML

Upvotes: 2

Misha
Misha

Reputation: 1826

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.

Upvotes: 8

Mad Hatter
Mad Hatter

Reputation: 772

If your database is local and you don't plant to make it client/server one day there could be little difference from a performance perspective. Much depends on what database you use. Some have "array DML" which would allow you to perform all the 360 inserts in one database round-trip, basically instead of doing 360 inserts you fill array bind variables and perform a single insert. The worst way would anyway be using n inserts without bind variables. Compiled and optimized Delphi code may be somewhat faster than a interpreted stored procedure code, if the database doesn't compile it (some may just use P-Code). From a design perspective, putting the data logic inside the DB publishing a sort of API via stored procedures (may forbidding other ways to modify data) may ensure a stronger control over data.

Upvotes: 0

phil
phil

Reputation: 707

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.

Upvotes: 5

Related Questions