Why are cursors generally slower in performance? What is the alternative approach for that to improve performance?

 Posted by Rajnilari2015 on 2/4/2016 | Category: Sql Server Interview questions | Views: 410 | Points: 40
Answer:

In the case of CURSORS the rows are rows are processed one after another.This consumes more memory and create page/row locks.To elaborate more on the point, the moment a cursor opens it loads a chunk of rows into memory and locks them.This creates a potential block.Then as we loop through the cursor we are making changes to other tables, performing some operations like insert/update/delete and still keeping all of the memory and locks of the cursor open.This cause the performance issues.Where as if we can re-write the same query by using in a SET Based manner, we can submit a complete batch of job(s) to the query engine which on the other hand will be processed much faster way.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response