Cursors Vs While loops

For an assignment i was required to process each row of the result set retrieved through a select query.
So cursors were more elegant i supposed and wrote a couple of cursors in an script. But there was a performance issue for the script. So after much a reading and discussing with my colleagues I came to a conclusion that a while loop is better in performance.

Cursors against While loops :

• Cursors are nothing but elegant while loops under the skin.
• Cursors are database objects so there is an overhead of creating them.
• Cursors take up memory as they are objects and also so as to loop through the result set.
• While loops are simplest constructs and do not take up memory

But sometimes depending upon the cursors and the data size there can be a huge difference in performance of Cursors(here being better) and While Loops.
So its for you to check out the performance for your script/query using different tools which one is better.
I would suggest using the While Loop because “Keep It Simple Silly”.

Tip: Use of temporary tables would be helpful in improving the performance of your while loop. Use of other performance optimizer techniques will give you an enhanced result with better performance.

Example:

1.] While Loop 

create #temp_table ( sr_no numeric(12,0) identity not null , column_1 int)
Comment : Here the identity attribute is to indicate sequential auto incremental value i.e the value for column ‘sr_no’  will get inserted automatically without explicitly specifying in the insert statement
insert into  #temp_table (column_1) values(197)
insert into  #temp_table (column_1) values(297)
insert into  #temp_table (column_1) values(397)
insert into  #temp_table (column_1) values(497)

declare @loop_counter
declare @total_count
set  @loop_counter=1
select  @total_count=count(column_1) from #temp_table
declare @temp_variable int
while(@loop_counter <= @total_count)
begin
select @temp_variable= column_1
from #temp_table where sr_no=@loop_counter
if  @temp_variable > 300
print “Value greater than 300”
else
print “Value less than 300”

set @loop_counter = @loop_counter +1
end

2.] Cursors

create #temp_table (  column_1 int)
insert into  #temp_table (column_1) values(197)
insert into  #temp_table (column_1) values(297)
insert into  #temp_table (column_1) values(397)
insert into  #temp_table (column_1) values(497)

declare loop_cur cursor
for select  column_1 from #temp_table

open  loop_cur cursor

declare @temp_variable int
fetch  loop_cur cursor into  : @temp_variable
while(@@sqlstatus==0)
begin
if  @temp_variable > 300
print “Value greater than 300”
else
print “Value less than 300”

fetch  loop_cur cursor into  : @temp_variable
end