将业务逻辑（c#）传递给事务（sql）会提高性能吗？
我们正在研究一种算法，该算法计算通过可变路线将资源从多个点移动到 X 点的最佳方式，过程如下：
1）获取所有可能的路线（数据库命中以获取解决方案中涉及的所有路线）
2）获取所有可能的起点
3) 结合所有路线构建双向图。
-----foreach起点----
4) 使用Hoffman Pavley算法计算k-最短路径（我们将其限制为一定数量的路径ei：前10条最短路径）
-----foreach路径为实际起点-----
5) 评估路线，计算我们可以从每个路线节点到目的地携带多少资源
6) 根据从每个路线节点移动的资源数量分配标点符号该可能解决方案涉及的点以及移动和转运（将资源从一种运输方式转移到另一种运输方式）的数量。
-----END foreach路径为实际起点-----
-----END foreach起点----
7) 返回按标点符号排序的可能解决方案
此逻辑的第一个版本花了大约 1 分钟时间计算解决方案。但在第二次修订中，我们发现遇到了很多 Select N+1 问题，因此我们优化了查询（不是全部），现在每次运行大约需要 3-10 秒，具体取决于变量的数量。
但现在有人建议传递所有逻辑来处理 SQL，并让 SQL Server 处理所有计算，他说，由于所有数据已经在 SQL Server 上，数据库将花费更少的时间来完成所有计算，从而避免所有计算选择N+1和延迟加载问题。他还担心并发性，多个用户运行此逻辑会导致应用程序服务器崩溃，但他说 sql-server 可以很好地处理这种负载。
我的观点：也许我们应该在尝试将 1500 行 C# 逻辑传递给 Transact SQL 之前尝试优化所有查询。更不用说，对于某些计算，我们使用双向图和 Hoffman Pavley 算法的第三方库，这些库在 Transact 中不可用，我们要么需要寻找 Transact 中已经编写的其他内容，要么自己实现所有逻辑。
注意：我们使用 Nhibernate 作为 ORM。
We are working an algorithm that calculate the optimal way to move resources from multiple points to point X through variable routes and the process go like this:
1) get all the possible routes (DB hit to get all the routes involved in solution)
2) Get all the possible starting points
3) Build a Bidirectional Graph combining all the routes.
-----foreach starting point----
4) Compute the k-shortest path using Hoffman Pavley algorithm (we limit this to a certain number of paths ei: the first 10 shortes paths)
-----foreach path for the actual starting point-----
5) evaluate the route calculating how much resources we can carry from every route node to the destination
6) Assign a punctuation depending on the numbers of resources moved form each point and the numbers of moves and trans-shipments (move resources from one transportation to another) involved in this possible solution.
-----END foreach path for the actual starting point-----
-----END foreach starting point----
7) return the posible solution ordered by punctuation
The first version of this logic took ~1min to calculate solutions. But on a second revision we found that we got a lot of Select N+1 problems, so we optimized the querys (not all of them) and now each run takes ~ 3-10 secs, depending on the numbers of variables.
But now someone suggested to pass all that logic to transact SQL, and let SQL server handle all that calculation, he said as all the data is already on the SQL Server it will take less time for the database to do all the calculation avoiding all the select N+1 and lazy loading problems. Also he concern about concurrency, multiple users running this logic will bring down the app server, but he said the sql-server can handle very well this kind of loads.
My opinion: maybe we should try to optimize all the querys before trying to pass 1500 lines of c# logic to Transact SQL. And not to mention that for some calculation we are using third party libraries for the Bidirectional Graph and Hoffman Pavley algorithm that are not available in transact, either we need to look for something else already wrote in transact or implement all that logic ourself.
NOTE: we are using Nhibernate as ORM.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我只会考虑将逻辑移至数据库作为最后的手段。
一个好的指南是在数据库中保留基于集合的处理，并在应用程序中保留迭代处理。您有许多 foreach 语句，除非它们可以被扁平化为集合操作，否则您将在数据库世界中真正遭受痛苦。
如果这是业务规则的应用程序，那么它应该位于应用程序层，除非有理由将其放入数据库。
将 1500 行代码移植到 TSQL 需要花费大量时间。如果 .NET CLR 是 MSSQL 的最新版本，则可以使用它，但根据我的经验，它比 Windows Server 上的 .NET 慢得多
预先提取所有所需数据以避免 N+ 应该相对简单1 选择；获取您需要的一切并将其全部连接到适当的对象图中。
最后，似乎所有请求都会重复前 4 个步骤。选择所有数据并处理前四个步骤，然后将图形保留在内存中可能是有意义的，从而避免为每个请求检索和预处理所有内容的显着前期影响。这可能不可能，但会完全消除数据检索问题。
I would only consider moving the logic to the database as a last resort.
A good guide is to keep set based processing in the database, and iterated processing in the application. You've got a number of foreach statements and unless they can be flattened into set operations you are really going to suffer in the database world.
If this is the application of a business rule then it should be in the application layer, unless there are reasons to put it in the database.
It will take a lot of time to port your 1500 lines to code to TSQL. You can use the .NET CLR if it's a recent version of MSSQL, but in my experience that's significantly slower than .NET on a Windows Server
It should be relatively simple to pull all your required data up front to avoid the N+1 selects; get everything you'll need and join it all into an appropriate object graph.
Finally, it seems like the first 4 steps are replicated for all requests. It might make sense to select all the data and process those first four steps and then keep the graph in memory, avoiding the significantly upfront hit of retrieving and preprocessing everything for each request. This may not be possible but would remove the data retrieval problem altogether.
很难深入了解如此普遍的优化问题，但
“由于所有数据都已经在 SQL Server 上，因此数据库完成所有计算所需的时间会更少”
这一说法不一定是正确的。如果您根本不更改逻辑，将 C# 代码直接移植到 t-sql 仍将运行同样多的查询，并且运行时间也一样长。您将节省在 SQL Server 和运行应用程序的计算机之间传输数据所需的时间，但这是否是瓶颈，或者是 SQL Server 实际运行所有这些查询所需的时间？每个查询的结果有多大？
另一个问题是 t-sql 在执行这里涉及的所有计算时会更快吗？在某种程度上，它们涉及迭代表中的数据并对该数据执行某些操作？我对此表示怀疑。根据实际处理的时间（而不是等待数据库），它甚至可能会更慢。
最重要的是，听起来翻译它会是一项巨大的努力，如果你甚至远程考虑这种方法，你应该做大量的测试来确定时间到底花在哪里，看看你可以获得什么（如果有的话）。
It's hard to provide insight on an optimization problem that is so general, but the statement:
"as all the data is already on the SQL Server it will take less time for the database to do all the calculation"
is not necessarily true. A straight port of your C# code to t-sql will still run just as many queries that will take just as long to run if you don't change the logic at all. You will save on the time it takes to transfer data between the SQL server and the machine running the app, but is that the bottleneck, or is it the time it takes the SQL server to actually run all these queries? How big are the results of each of these queries?
The other question is would t-sql be any faster at doing all the calculations involved here, to the extent that they involve iterating through data in tables and doing something with that data? I doubt it. Depending on how much of the time is actually processing (rather than waiting for the database) it could even be slower.
Bottom line is, it sounds like translating it would be a huge effort, if you are even remotely considering this approach you should do a lot of tests to determine exactly where the time is going and see what you could stand to gain, if anything.
事情是这样的：
将逻辑转移到数据库通常可以提高像您这样的复杂报告要求的性能。这是通过更好地对数据建立索引来实现的，这样索引意味着大部分工作（即：排序）已在插入时为您完成。
由于排序工作是在插入所需索引时完成的，因此最终会导致插入和其他写入操作变慢。对于需要做的不仅仅是报告的系统来说，这通常是有害的。
此外，在某些时候，您可能需要考虑应用程序的扩展方式。当您这样做时，请考虑您的数据库服务器可能已经是最昂贵的服务器，也是升级最昂贵的服务器。仅许可成本就会让您的预算经理不太愿意升级数据库服务器。数据库通常也很难在集群中工作。与数据库相比，添加 Web 或应用程序服务器并让它们在农场中工作就像在公园散步一样简单。由于这些原因，您可以采取任何措施来释放数据库的性能压力，都可能会改善应用程序的扩展方式。
Here's the deal:
Shifting logic to the database can often lead to improved performance on complex report requirements such as yours. This is accomplished through better indexing of the data, such that the index means much of the work (namely: sorting) is done for you at insert time.
Since the sorting work is done at insert time for the indexes you need, you end up with slower inserts and other write operations. This can often be detrimental on a system that needs to do more than just your reports.
Additionally, at some point you'll want to think about how your app scales. When you do that, consider that your database server is likely already your most expensive server, as well as the most expensive server to upgrade. Licensing costs alone will make upgrading your database server less palatable to your budget manager. Databases are also typically harder to get working in a cluster. Compared to the database, adding web or application servers and getting them to work in a farm is a walk in the park. For these reasons, anything you can do to release performance pressure from your database is likely to improve the way your app will scale.
我同意“我只会考虑将逻辑移至数据库作为最后的手段”。上面写的。
如果使用 CLR 程序集，则可以将第三方库包含到 Transact SQL 中，因此这不是问题。
从资源的角度来看，扩展应用程序服务器通常比扩展数据库服务器更容易（复制？）。因此，如果明天这些调用是今天调用的 X 10 或 X 50，我们是否确定您的数据库服务器仍会在可接受的时间进行计算和其他任何操作？
从性能角度来看，只需优化 SQL，时间就从 1 分钟缩短到 5 秒。显然，如果您在单独的 SQL 引擎中使用非优化的 SQL，那么与在单独的 SQL 引擎中使用优化的 SQL 仍然有区别。
我建议重点优化 SQL 和 C# 引擎。我猜这N+1个案例是骨干，在完成前一个案例之前你无法获得记录。尽管如此，您可以提前选择的任何内容都会提高性能 - 您最好通过 3 个选择返回总共 1000 条（在 C# 中过滤 10 条）记录来获得 10 条记录，而不是通过 10 个选择返回总共 10 条记录。
I would agree to "I would only consider moving the logic to the database as a last resort." written above.
Third party libraries can be included into Transact SQL if you use CLR assemblies, so this is not a problem.
From resources perspective it is usually easier to expand your application servers than your database server (replication?). So if tomorrow those calls go X 10 or X 50 of today's calls, are we sure your database server will still do the calculations and anything else at acceptable times?
From performance perspective, you go down from 1 min to 5 sec with just optimizing SQL. Obviously if you used the non optimized SQL in an SQL alone engine you still had a difference than using your optimized SQL - again in an SQL only engine.
I would suggest to focus on optimizing SQL and the engine at c#. Those N+1 cases i guess are the backbone and you cannot get the record before you finalize the previous one. Still anything you can select in advance is a performance gain - you better get 10 records with 3 selects returning a total of 1000 (filtering the 10 within C#) records than with 10 selects returning a total of 10 records.
将
所以我的观点是，在将所有逻辑迁移到数据库之前，您应该尝试优化查询。
Moving logic to SQL may help but it has the cost:
So my opinion is that you should try to optimize your queries before migrating all logic to database.