Vakul's .NET Realm

My journey towards .NET World…

  • Details

    Vakuls MCPD Certification Vakuls MCTS Certification Author's Pic



  • Blog Stats

    • 142,380 hits
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 355 other followers

  • Follow me in Twitter:




  • qrcode
  • Subscribe

  • del.icio.us:

Cursor Disadvantages

Posted by Vakul Kumar More on September 9, 2010


A conventional idea every experienced developer projects is : CURSOR is evil. Recently, I had a situation where I had to look into different options for achieving a task and many have said Cursors would the best option.

I started researching more on Cursors, as I was bit confused on contradictory ideas about cursors. A cursor is a memory resident set of pointers that reference data in your result set which has the data from the base tables. Cursors have a bad reputation because they are typically used by every Junior SQL Developer in search of an easy solution and they do not perform well and they deplete systems resources.

I personally feel SQL Server by default have a direct performance degradation on the usage of cursors. SQL ServerEngine processes records or data in such a way it will have a performance impact when cursor is used.  All other parallel processes will suffer when cursor is in action and they are the SLOWEST way to access data as it operates by checking row by row operations. Cursors are 30 times slower than any other alternatives.

I adopted an alternative way to accomplish my task.

Then why is Cursor needed ? There are few scenarios where we can use Cursors:

  • When you need to fire a stored procedure once per row in a table. In other words, if we need to perform row-by-row operations rather than set based operations
  • Query optimizer helps in achieving Adaptability and Efficiency by adapting itself.
  • When row wise validations are needed for less data.

I will keep on adding any new observations about Cursors in next few days.

Happy Coding

Advertisements

2 Responses to “Cursor Disadvantages”

  1. I am so happy to read this. This is the kind of manual that needs to be given and not the random misinformation that is at the other blogs. Appreciate your sharing this best doc.

  2. Omprakash said

    I have faced lot of problem with cursors recently, then i have changed my code from cursors to while loop which has made my procedure execution about 70% faster.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: