fast farward curson vs while loop on @table

Question

fast farward curson vs while loop on @table

rajemessage 14195

Hall of Fame

Points: 3351
More actions
November 8, 2025 at 12:04 pm

#4682527

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.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63627 More actions · Answer 1

What happened when you tried it?

with only 15 records, I'm not sure you'll see a huge difference, but give it a try and record both times. I didn't write this to be mean or ugly, but you learn most by doing - by trying multiple options and seeing which one works best.

frederico_fonseca SSCoach Points: 16290 More actions · Answer 2

but main question is why you need a cursor/while loop?

Steve Jones - SSC Editor SSC Guru Points: 740241 More actions · Answer 3

Frederico has the best question. Do you need a loop? What's the problem and data? Can you post or mock this up?

As pietlinden noted, 15 rows might not matter, but it might. If this runs 1,000s of times a second, then maybe you care. What I'd do is test this at scale. Either over and over (loop over your loop) or add data and try 1.5mm rows. Then compare which works better.

carwalk Newbie Points: 8 More actions · Answer 4

rajemessage 14195 wrote:

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.

For about 15 rows, there is no meaningful performance difference between a FAST_FORWARD cursor and a WHILE loop over a table variable, because both will execute so fast that the overhead is measured in microseconds and is completely dwarfed by whatever work you do inside the loop. A FAST_FORWARD cursor still has setup and fetch overhead, while a WHILE loop has repeated lookup and control-flow overhead, but at such a small row count these differences are irrelevant in practice. What matters far more is whether the logic inside the loop can be rewritten in a set-based way (which will always outperform both), whether you are using a table variable versus a #temp table, and how well the underlying tables are indexed. As a rule of thumb, for tiny datasets like yours you should simply choose the approach that is clearer and easier to maintain; if row-by-row processing is truly required for larger sets, a FAST_FORWARD cursor is usually cleaner than a DIY loop, but for 15 rows either choice is effectively “free” from a performance standpoint.