哪个更快在SQL，While循环，递归存储过程或游标？ [英] Which is faster in SQL, While loop, Recursive Stored proc, or Cursor?
问题描述
在SQL，While循环，递归存储过程或Cursor中哪个更快？
我想在存储过程中的几个点优化性能。
我将优化的代码格式化一些字符串以输出到文件。
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:
- 使用基于集合的方法（内部选择，联接，联合等）
- CTE（对于有经验的用户清晰易于管理，对初学者有点阴影）
- 使用控制流语句（如果，...）
- 按照顺序使用光标（程序代码，易于关注）
- Using set-based approach (inner selects, joins, unions and such)
- Using CTE (clear and manageable for an experienced user, bit shady for a beginner)
- Using control-flow statements (if, while...)
- Using cursors (procedural code, easy to follow)
。
如果代码使用的频率少得多，我可能会在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, it's like a sex convention at a catholic monastery*
并且，不要忘记，代码只有你的数据一样快。
And, do not forget, the code is only as fast as your data. There is no substitution for good indexing.
- 这种比喻在几年前很有趣，现在有点伤心。 li>
- that analogy was a lot funnier a couple of years ago, now it's a bit sad.
这篇关于哪个更快在SQL，While循环，递归存储过程或游标？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
