SQL、While 循环、递归存储过程或游标中哪个更快?

问题描述

在 SQL、While 循环、递归存储过程或游标中哪个更快?我想优化存储过程中几个点的性能.我正在优化的代码格式化了一些字符串以输出到文件.

Which is faster in SQL, While loop, Recursive Stored proc, or Cursor? I want to optimize the performance in a couple of spots in a stored procedure. The code I'm optimizing formats some strings for output to a file.

推荐答案

我假设您使用的是 SQL Server.

I'll assume you are using SQL Server.

首先，正如有人在语句中所说，递归存储过程虽然可能，但由于堆栈大小的原因，在 SQL Server 中并不是一个好主意.所以，任何深度递归的逻辑都会崩溃.但是，如果您最多有 2-3 级嵌套，则可以尝试使用递归或使用 CTE，这也有点递归(SQL Server 2005 及更高版本).一旦您设法了解 CTE，这是一项非常有用的技术.我没有测量过，但在我使用 CTE 的几个地方从来没有出现过性能问题.

First of all, as someone said in the statements, recursive stored procs, while possible, are not a good idea in SQL Server because of the stack size. So, any deeply recursive logic will break. However, if you have 2-3 levels of nesting at best, you might try using recursion or using CTE, which is also a bit recursive (SQL Server 2005 and up). Once you manage to wrap your head around CTE, it's an immensely useful technique. I haven't measured, but I've never had performance issues in the few places where I used CTE.

另一方面，游标是大性能猪，所以我 (和一半的互联网) 建议不要在经常调用的代码中使用它们.但是由于游标更像是一种经典的编程结构，类似于 C# 中的 foreach，有些人发现使用游标进行数据操作的 SQL 代码更容易查看、理解和维护，而不是一些复杂的多重 -内部选择 SQL 的问题，所以在偶尔调用的代码中使用它们并不是最坏的主意.

Cursors on the other hand are big performance hogs, so I (and half the internet) would recommend not to use them in code that is called often. But as cursors are more a classical programming structure, akin to a foreach in C#, some people find it easier to look at, understand and maintain SQL code that uses cursors for data manipulation, over some convoluted multiple-inner-select SQL monstrosity, so it's not the worst idea to use them in code that will be called once in a while.

说到while，它也将编程思维从基于集合的思维转变为基于过程的思维方式，所以虽然它相对较快且不消耗大量资源，但仍然可以显着地增加您向数据库本身发出的数据操作语句的数量.

Speaking of while, it also transfers the programming mindset from a set-based one, to a procedure-based one, so while it's relatively fast and does not consume lots of resources, can still dramatically increase the number of data manipulation statements you issue to the database itself.

总而言之，如果我必须制作一个性能至上的复杂存储过程，我会尝试:

To summarize, if I had to make a complex stored proc where the performance is paramount I'd try:

1. 使用基于集合的方法(内部选择、连接、联合等)
2. 使用 CTE(对于有经验的用户来说清晰且易于管理，对于初学者来说有点阴暗)
3. 使用控制流语句(if、while...)
4. 使用游标(程序代码，易于遵循)

按那个顺序.

如果代码使用得少得多，我可能会在 1 和 2 之前移动 3 和 4，但同样，仅适用于使用大量表和大量关系的复杂场景.当然，YMMV，所以我会测试我在现实世界场景中所做的任何程序，以实际衡量性能，因为，我们可以谈论直到我们面无表情为止，这是快的，那是慢的，但是直到您会得到真实的测量结果，但无法判断变化是让事情变得更好还是更糟.

If the code is used much less often, I'll probably move 3 and 4 before 1 and 2, but, again, only for complex scenarios that use lots of tables, and lots of relations. Of course, YMMV, so I'd test whatever procedure I make in a real-world scenario, to actually measure the performance, because, we can talk until we are blue in the face about this is fast and that is slow, but until you get real measurements, there is no way to tell whether changes are making things better or worse.

而且，不要忘记，代码的速度与您的数据一样快.良好的索引是无可替代的.

And, do not forget, the code is only as fast as your data. There is no substitution for good indexing.

这篇关于SQL、While 循环、递归存储过程或游标中哪个更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！