This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 42%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERK%3C/text%3E%3C/svg%3E)
hi,
need to known weather fast farwand cursor is faster than a while loop made on @table variable haveing less than 15 rows that means 15 iteration.
pls tell me which one is faster.
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Probably experimenting in real conditions will confirm the assumptions and evidence.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
With just 15 rows, it probably doesn’t matter. Use which creates the cleanest code. That said you really should do a single set operation rather than while loop.
Generally, the fastest is to work will all data at once in a single statement, or a suite of statement rather than working with one row at a time.
However, there are situations where this not easy to do, for instance you want to call a stored procedure that uses scalar input.
For that situation, I prefer to write the loop this way:
DECLARE @cur CURSOR
SET @cur = CURSOR STATIC FOR
SELECT .... FROM ... ORDER BY ...
OPEN @cur
WHILE 1 = 1
BEGIN
FETCH @cur INTO ....
IF @@fetch_status <> 0
BREAK
EXEC my_sp ...
END
As you can see, this includes both a cursor and a WHILE loop. The cursor is STATIC which means that the SELECT statement is evaluated once and the result is copied to a temp table and the cursor is served from that temp table. I prefer STATIC over FAST_FORWARD, since I have never been able to understand what that means. The documentation says "dynamic with optimizations", and a dynamic cursors are scary: they evaluate the condition for every FETCH and can be very slow.
When you ask whether a cursor is faster then a WHILE loop, I guess that what you have in mind a loop there you use some other mechanism to get the next row, for instance SELECT TOP 1. Such a loop can be slightly faster than a cursor loop, if it is written in the right way. For instance, it is critical that there is an index to support the retrieval of the next row. But I've seen too many cases where the loop was over a temp table with no index at all, so there had to be a table scan for every iteration. Even for a table with just a couple of ten of thousands of rows, this can be very costly. For this reason I prefer static cursors - they are safer.
For 15 rows it less of a matter. For the iteration itself that is. If the operation inside the loop is expensive, there is little reason to repeat it 15 times, if all 15 rows could be handled at once.
what if i make a @ table that is table variable for 1000 rows and then give this @ table to fast farward cursor rather than static.
As Viorel said, test it out yourself. Here is a script to get you started:
SET NOCOUNT ON
DECLARE @table TABLE (id int NOT NULL PRIMARY KEY,
data char(36) NOT NULL)
INSERT @table(id, data)
SELECT value, convert(char(36), newid())
FROM generate_series(1, 100000)
DECLARE @cur CURSOR,
@d datetime2(3) = sysdatetime(),
@id int,
@data char(36)
SET @cur = CURSOR FAST_FORWARD FOR -- Test also with STATIC
SELECT id, data FROM @table
OPEN @cur
WHILE 1 = 1
BEGIN
FETCH @cur INTO @id, @data
IF @@fetch_status <> 0
BREAK
END
SELECT datediff(ms, @d, sysdatetime()) AS duration
On my machine, FAST_FORWARD seems be slightly faster, which is not surprising since STATIC includes copying the data elsewhere. With permanent tables and a more complex query with joins etc it may be different. And particularly, what if the cursor sees updates that occurred after the cursor started? For instance, updates you did yourself?
In general, fast-forward cursors are designed to be more efficient than traditional cursors because they are read-only and do not support data modifications. However, when comparing performance between a fast-forward cursor and a WHILE loop for a small number of iterations (like 15 rows), the performance difference may not be significant.
For small datasets, a WHILE loop can be quite efficient and may even perform comparably to a fast-forward cursor. The choice between the two often depends on the specific use case and the operations being performed. If the logic can be expressed in a set-based manner, that is usually the preferred approach for performance reasons.
In summary, while fast-forward cursors may have advantages in larger datasets or more complex operations, for a small number of rows (like 15), both options could perform similarly, and the best choice may depend on the specific context of your application.