6

what criteria should we keep in mind while choosing which of the above options when there's a need to loop through table/data.

Bikkar
  • 77
  • 1
  • 1
  • 5
  • 3
    Relational databases _generally_ perform most efficiently when processing in sets [More Info](http://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursors). So neither approach appeals to me. There is a time and a place for a cursor and loop; but most of the time if you can processes using set based logic your performance will be significantly better. – xQbert Jan 13 '16 at 20:45
  • 1
    Consider use "is it a best practice to use while loop vs Cursor?" for title as it's more clear and will appeal to more user for later use. – AXMIM Jan 13 '16 at 20:45
  • 3
    Aren't cursors normally used with while loops? – Gordon Linoff Jan 13 '16 at 20:48
  • 1
    sorry i didn't get your point AXMIM, can you please explain? – Bikkar Jan 13 '16 at 20:48
  • 1
    @Bikkar AXMIM is suggesting a title change to your question – xQbert Jan 13 '16 at 20:51
  • From personal experience, "cursor" will perform better if done properly. However, I would consider "while" loop when cursor update data that is read or updated frequently elsewhere. This to minimize the time record is locked. This is completely from personal experience and I may be totally wrong. Back in sql server 2005, I always used While instead of cursors until I found out cursor were faster. But I don't know if there is a best practice about that. – AXMIM Jan 13 '16 at 20:59
  • @Bikkar, i'm suggesting you change the title of your question. It will be more clear that way what your question is. – AXMIM Jan 13 '16 at 21:00
  • oh got it, thanks AXMIM, i'll rephrase my question. thanks for your reply as well :) – Bikkar Jan 13 '16 at 21:01

1 Answers1

6

WHILE loop and cursor perform similarly poorly.

Seeing as you're using SQL you're probably going to execute some SQL commands inside the loop. The DB engine is geared toward getting a lot done in batch with one SQL command. Having a loop fire thousands of tiny ones won't actually be tiny. The overheads for each one are too hefty.

Most things you might first think to do with a cursor or loop can be done another way in SQL though they may require you work out what temporary tables you're going to make to help out.

This is helpful: How to think in SQL?

Adamantish
  • 1,888
  • 2
  • 20
  • 23