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.
我正在寻找处理这种情况的最佳方法。我想在数据库表中存储摊还计划。每行包含日期，当前余额，付款，市政，利息和新余额。对于典型的30年抵押贷款，这将是360行或数据库插入。
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?
我应该使用Delphi在循环内进行计算并为每个结果执行插入操作，还是应该在存储过程中执行这些计算？
This would be a single user, local machine, desktop application.
这将是单个用户，本地计算机，桌面应用程序。
4 个解决方案
#1
5
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.
此外，通过将所有与数据相关的操作保留在数据库中，如果将来某个时候您选择切换语言，则可以节省编码的麻烦。
#2
8
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.
所以，如果您的东西是数据库和SQL，并且您对此感到满意，那么存储过程就可以了。但是，如果您主要是应用程序开发人员，那么使用存储过程而不是从代码执行查询时，我看不到任何好处。
#3
2
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.)
如果您碰巧使用AnyDAC，它支持所有支持的数据库的ArrayDML。我认为这是一个很好的功能。这是商业软件，但投资很好。 （除非作为非常满意的客户，否则我不会以任何方式与他们联系。）
See Very High Performance using the Array DML
使用Array DML查看Very High Performance
#4
0
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.
如果您的数据库是本地数据库，并且您没有将其设置为客户端/服务器，那么从性能角度来看几乎没有什么区别。很大程度上取决于您使用的数据库。有些具有“数组DML”，它允许您在一个数据库往返中执行所有360插入，基本上不是执行360插入，而是填充数组绑定变量并执行单个插入。无论如何，最糟糕的方式是使用没有绑定变量的n个插入。编译和优化的Delphi代码可能比解释的存储过程代码快一些，如果数据库不编译它（有些可能只使用P代码）。从设计的角度来看，将数据逻辑放在DB中通过存储过程发布一种API（可能禁止修改数据的其他方法）可以确保更强大的数据控制。
#1
5
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.
此外，通过将所有与数据相关的操作保留在数据库中，如果将来某个时候您选择切换语言，则可以节省编码的麻烦。
#2
8
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.
所以，如果您的东西是数据库和SQL，并且您对此感到满意，那么存储过程就可以了。但是，如果您主要是应用程序开发人员，那么使用存储过程而不是从代码执行查询时，我看不到任何好处。
#3
2
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.)
如果您碰巧使用AnyDAC，它支持所有支持的数据库的ArrayDML。我认为这是一个很好的功能。这是商业软件，但投资很好。 （除非作为非常满意的客户，否则我不会以任何方式与他们联系。）
See Very High Performance using the Array DML
使用Array DML查看Very High Performance
#4
0
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.
如果您的数据库是本地数据库，并且您没有将其设置为客户端/服务器，那么从性能角度来看几乎没有什么区别。很大程度上取决于您使用的数据库。有些具有“数组DML”，它允许您在一个数据库往返中执行所有360插入，基本上不是执行360插入，而是填充数组绑定变量并执行单个插入。无论如何，最糟糕的方式是使用没有绑定变量的n个插入。编译和优化的Delphi代码可能比解释的存储过程代码快一些，如果数据库不编译它（有些可能只使用P代码）。从设计的角度来看，将数据逻辑放在DB中通过存储过程发布一种API（可能禁止修改数据的其他方法）可以确保更强大的数据控制。