fast farward curson vs while loop on @table

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.

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.

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.