sql游标 while_用SQL Server中的排名函数替换SQL While循环和游标，以提高查询性能

sql游标 while

SQL While loop and cursor are the most common approach to repeat a statement on condition-based or determined limits. Loop and cursor can be utilized in a circumstance to deal with row-based processing in T-SQL. We generally observe moderate execution of old made procedures, which are composed of using loop and cursors. Those procedures take time, especially when the number of iteration count is big for the execution.

SQL While循环和游标是在基于条件或确定的限制上重复语句的最常见方法。 在某种情况下，可以使用循环和游标来处理T-SQL中基于行的处理。 我们通常观察到旧程序的适度执行，这些旧程序由使用循环和游标组成。 这些过程需要时间，尤其是在迭代次数对于执行来说很大的时候。

Always confusing thing is which one is better; SQL While loop or cursor? While SQL While loop is quicker than a cursor, reason found that cursor is defined by DECLARE CURSOR. Every emphasis of the loop will be executed inside system memory and consuming required server assets. On the off chance that the iteration count is extremely high, at that point, system memory occupation can be raised, and it can affect other SQL Server threads as well. Here, we will clarify in detail query performance issues at the database level, which can be raised with the utilization of the loop.

总是让人困惑的是哪一个更好。 SQL While循环还是游标？ 尽管SQL While循环比游标快，但发现游标由DECLARE CURSOR定义。 循环的所有重点都将在系统内存中执行并消耗所需的服务器资产。 如果迭代计数极高，那么此时可能会增加系统内存占用，并且也可能影响其他SQL Server线程。 在这里，我们将详细阐明数据库级别的查询性能问题，这可以通过使用循环来解决。

The difficulty of writing a small piece of code ought not to be the essential factor to utilize that sort of code. We have used ROW_NUMBER(), RANK() and DENSE_RANK() capacities to separate each line. This will be a better approach for query execution in code optimization. We can’t use these functions as a solution for every problem, except most of the use of the loop could be changed over into a single statement.

编写一小段代码的困难不应成为利用这种代码的必要因素。 我们使用了ROW_NUMBER（），RANK（）和DENSE_RANK（）容量来分隔每行。 这将是在代码优化中执行查询的更好方法。 我们不能将这些函数用作每个问题的解决方案，除非可以将循环的大多数用法转换为单个语句。

If iteration of the SQL While loop is reliant on the next iteration or previous iterations, at that point, we can’t utilize ranking functions for that yet as a recursive CTE is a decent option for SELECT explanations. For instance, the result or activity of the loop – iteration is getting used in the next iteration at that point ranking function can’t deal with the circumstance. Prior to the clarification of ranking on the loop, the developer should mindful of the ranking function and its behavior.

如果SQL While循环的迭代依赖于下一个迭代或上一个迭代，那么此时，我们不能使用排名函数，因为递归CTE是SELECT解释的一个不错的选择。 例如，循环的结果或活动–迭代将在下一次迭代中使用，此时点排序功能无法处理这种情况。 在澄清循环上的排名之前，开发人员应注意排名功能及其行为。

什么是ROW_NUMBER，RANK和DENSE_RANK，在哪里可以使用它们？ (What are ROW_NUMBER, RANK and DENSE_RANK and where one can use them?)

Ranking functions provide a ranking value for each row either in a partition or not. Based on the rank, we can manipulate the query result as required. Each function has a distinct purpose of use and result format:

排序功能为分区中的每一行或不为分区中的每一行提供排名值。 根据排名，我们可以根据需要操纵查询结果。 每个函数都有不同的用途和结果格式：

ROW_NUMBER（） (ROW_NUMBER())

The ROW_NUMBER() function is used to get a sequential number row with partition and shorting on the column, which will begin from 1. PARTITION sections are being utilized in the ranking function to isolate sequences for the different values of the partition columns. What’s more, the sequence of the number is increasing with the defined shorting with the ORDER BY in ranking function.

ROW_NUMBER（）函数用于获取从1开始的带有分区和该列短路的序列号行，该行将从1开始。PARTITION部分在排名函数中用于隔离分区列的不同值的序列。 而且，随着排序功能中ORDER BY的定义的短路，数字的顺序会增加。

The ranking sequence won’t have any gap or duplicate value for the partition column value and the order, which is defined in the OVER() statement.

对于OVER（）语句中定义的分区列值和顺序，排序序列不会有任何间隙或重复值。

秩（） (RANK() )

The RANK() function is used to get a sequential number row with partition and shorting on the column with the sequence gap. The equivalent sequential number can exist with multiple records and a sequential number can have value gaps in sequence number when a duplicate value exists in the partition or shorting over the section, which is being defined in the OVER() proviso.

RANK（）函数用于获取具有分区的序列号行，并在具有序列间隔的列上进行短路。 当分区中存在重复值或在该部分上存在重复值时，等效序号可以与多个记录一起存在，并且序号在序列号中可能存在值间隙，这在OVER（）条款中已定义。

We can see sequence number is getting skipped in the Rank_ column in the above result set.

我们可以看到在以上结果集中的Rank_列中跳过了序列号。

DENSE_RANK（） (DENSE_RANK() )

The DENSE_RANK() is used to get sequential number row with partition and shorting on the column without a sequence gap. The same sequential number can exist with multiple records when a duplicate value exists in the partition or shorting over the column, which is being defined in the OVER() provision.

DENSE_RANK（）用于获取具有分区的连续编号行，并且在没有序列间隙的情况下缩短该列。 当在OVER（）条款中定义的分区中或该列上存在重复值时，重复记录可以存在于多个记录中。

We can see sequence without getting skipped in the DenseRank_ column in the above outcome set.

我们可以在上面的结果集中的DenseRank_列中看到序列而不会被跳过。

We have one more guide to get greater clearness on the difference between these three ranking functions with the below result set. What’s more, as next, what ways can be executed to avoid the SQL While loop and how? We will talk about here with specific models and dependent on that we can utilize it in future scope:

我们还有另外一本指南，可通过以下结果集更清楚地了解这三个排名函数之间的区别。 此外，接下来，可以执行哪些方法来避免SQL While循环，以及如何执行？ 我们将在这里讨论特定的模型，并依赖于我们可以在将来的范围内使用它：

SELECT *,
  ROW_NUMBER() OVER(PARTITION BY Subject_id ORDER BY marks) AS Row_Number,
  RANK() OVER(PARTITION BY Subject_id ORDER BY marks) AS Rank,
  DENSE_RANK() OVER(PARTITION BY Subject_id ORDER BY marks) AS Dense_rank
FROM result

SQL While循环在SQL Server中的工作方式 (How SQL While loop works in SQL Server)

Most metrics, which are being used in a SQL While loop is can be accomplished by a single statement, yet a few can’t. For instance, as I said prior, in a sequence of transactions or events, if any operation or action is subject to the previous one after being endured by the same in a sequence then we can’t make it to a single proclamation. We can supplant SQL While loop and cursor with recursive CTE to make it straightforward code structure but query performance-wise, no strange.

在SQL While循环中使用的大多数指标都可以通过一条语句来完成，而有些则不能。 例如，正如我之前说的，在一系列事务或事件中，如果任何操作或动作在经历了相同的顺序后又要服从上一个操作或动作，那么我们就无法作出单一声明。 我们可以使用递归CTE取代SQL While循环和游标，以使其代码结构简单明了，但在性能方面进行查询，这并不奇怪。

Data manipulation and evaluation by partitioning over any column, which is being referenced by the counter of the loop and executing each one in an iteration as independent execution that can be achieved using a couple of Windows Functions in SQL Server. I attempted a couple of ranking functions to replace a few loop uses.

通过对任意列进行分区来对数据进行操作和评估，该列被循环计数器引用，并在迭代中作为独立执行来执行每个操作，这可以使用SQL Server中的几个Windows函数来实现。 我尝试了一些排名函数来代替一些循环使用。

SQL Server中的循环如何工作并影响查询性能？ (How a loop in SQL Server works and affects query performance?)

Loop defines in three sections: Initiate, Condition and Increment. In SQL Server, a loop is characterized to perform such queries with the number of cycles in the body. Those operations can be Select, Insert, Update or Delete tasks on the table:

循环在三个部分中定义：启动，条件和增量。 在SQL Server中，循环的特征是使用主体中的循环数执行此类查询。 这些操作可以是表上的“选择”，“插入”，“更新”或“删除”任务：

i.e. FOR (I = 1, I <= 10, I ++)
iteration I = 1
————-
Fetch value to be used in iteration scope for (i=1)
Data Manipulation for (i=1) OR
INSERT/UPDATE/DELETE for (i=1) OR
Insert into Temp Table to return at the end of the loop execution

即FOR（I = 1，I <= 10，I ++）
迭代I = 1
————-
获取要在（i = 1）的迭代范围中使用的值
（i = 1）的数据处理或
插入/更新/删除（i = 1）或
插入临时表以在循环执行结束时返回

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table ‘Table_name’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server执行时间：CPU时间= 0毫秒，经过的时间= 0毫秒。 表“ Table_name”。 扫描计数0，逻辑读0，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

—
iteration I = 2
————-
DO the same for I = 2 as I = 1

-
迭代I = 2
————-
对I = 2和I = 1执行相同的操作

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table ‘Table_name’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server执行时间：CPU时间= 0毫秒，经过的时间= 0毫秒。 表“ Table_name”。 扫描计数0，逻辑读0，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0。

—
—
iteration I = 10
————–

-
-
迭代I = 10
——————

For example, we began a SQL While loop with 1 and increased with 1 till the variable @i reach the 10. What can be the query performance issue regarding execution? For every iteration, a set of T-SQL statements will be executed, which is defined in the body. T-SQL statements can be Insert, Update, Delete and Select operations. Be that as it may, each time iteration execution expends server resources, logical and physical I/O. There can be such execution readings with the logical reads, physical reads, scan count and many more. We can see those readings in the execution plan. Along these lines, it sets aside some effort to finish every execution of the iteration:

例如，我们从1开始执行SQL While循环，然后增加1直到变量@i达到10。关于执行的查询性能问题可能是什么？ 对于每次迭代，将执行一组在正文中定义的T-SQL语句。 T-SQL语句可以是插入，更新，删除和选择操作。 尽管如此，每次迭代执行都会消耗服务器资源，逻辑和物理I / O。 逻辑读取，物理读取，扫描计数等等可能会有这样的执行读取。 我们可以在执行计划中看到这些读数。 按照这些原则，它会花一些精力来完成每次迭代的执行：

declare @i INT = 1
WHILE(@i <= 10)
BEGIN
  --T-SQL Queries
  SET @i = @i + 1
END;

Below the reference table (user_term_point) represented with few rows to explain some use case to use the loop and overhead it with ranking function or GROUP BY:

在参考表（ user_term_point ）下方，该行以很少的行表示，以解释一些使用情况，以使用循环并将其与排名函数或GROUP BY一起使用：

If a user wants to fetch user wise manipulated information with applying the logic, at that point, iteration can be defined on the number of distinct users in the table and afterward apply the logic to get a specific outcome. For instance, the second lowest and highest or third lowest and highest point for the user at that point ranking function causes us to get it:

如果用户希望通过应用逻辑来获取用户明智的操纵信息，则此时可以在表中不同用户的数量上定义迭代，然后再应用逻辑以获得特定结果。 例如，用户在该点排名功能上的第二最低和最高点或第三最低和最高点使我们得到了它：

SELECT user_id, term, point
FROM (
SELECT user_id, 
    term,
    point, 
    ROW_NUMBER() OVER(PARTITION BY user_id, term ORDER BY point) AS lowest_score_rank
    ROW_NUMBER() OVER(PARTITION BY user_id, term ORDER BY point DESC) AS highest_score_rank
FROM user_term_point)TABLE
WHERE lowest_score_rank IN (2, 3)

As clarified above, if multiple users have similar points for a similar term, then ROW_NUMBER() returns a single record for each sequence number. The RANK() and DENSE_RANK() can be used to get different users for a similar term.

如上面所阐明的，如果多个用户在相似的词项中具有相似的点，则ROW_NUMBER（）将为每个序列号返回一条记录。 可以使用RANK（）和DENSE_RANK（）获取相似术语的不同用户。

结论 (Conclusion)

Here, the conclusion is that for each iteration, there will be logical reads, scan count over the table and its tedious as well. Since SQL While loop executes a similar proclamation multiple times whether this functions or GROUP BY returns information within a single execution.

此处的结论是，对于每次迭代，将进行逻辑读取，对表的扫描计数及其繁琐的操作。 由于SQL While循环多次执行类似的声明，因此无论此函数还是GROUP BY在一次执行中都返回信息。

翻译自: https://www.sqlshack.com/replace-a-sql-while-loop-and-a-cursor-with-ranking-functions-in-sql-server-for-better-query-performances/

sql游标 while