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.
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.
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.
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.
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()
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.
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.
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.
We can see sequence number is getting skipped in the Rank_ column in the above result set.
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.
We can see sequence without getting skipped in the DenseRank_ column in the above outcome set.
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:
|
1 2 3 4 5 |
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 |
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.
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.
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:
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
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.
—
iteration I = 2
————-
DO the same for I = 2 as 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.
—
—
iteration 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 2 3 4 5 6 |
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:
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:
|
1 2 3 4 5 6 7 8 9 |
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.
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.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020