Why Cursors so bad

Question

Post reply

Why Cursors so bad

Eswin

SSCrazy Eights

Points: 8320
More actions
June 24, 2009 at 9:36 pm

#201833

Hi Expertz
Can anyone explain to me why cursor take longer time for execution.
Why cursors effect performance.
How to avoid clustered and non-clustered index Scan's and also table scans.
Which is better if i do not need to update cursor result set:
DECLARE c1 CURSOR
or
DECLARE c1 CURSOR READ_ONLY
or
DECLARE c1 CURSOR FAST_FORWARD READ_ONLY
or
DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY
I need expert opinions on this , that's why i posted it here rather than searching in google .
Please help
Tanx 😀

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Start here[/url].

Then here[/url].

Any questions after that, ask right back here or in the discussion threads of the articles above.

Eswin SSCrazy Eights Points: 8320 More actions · Answer 2

How to avoid clustered and non-clustered index Scan's and also table scans.

Which is better if i do not need to update cursor result set:

DECLARE c1 CURSOR

or

DECLARE c1 CURSOR READ_ONLY

or

DECLARE c1 CURSOR FAST_FORWARD READ_ONLY

or

DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY

Tanx 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Have you read the articles that I asked you to read yet in my post above? If not, please go read them.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Eswin (6/25/2009)
How to avoid clustered and non-clustered index Scan's and also table scans.

Indexes that support the queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

Eswin (6/25/2009)
How to avoid clustered and non-clustered index Scan's and also table scans.

Do what Gail says. Avoid functions on columns in the WHERE clause and JOINs. Use operations that can use indexes, avoid operations that can't... There's more. Just keep reading.

Which is better if i do not need to update cursor result set:
DECLARE c1 CURSOR
or
DECLARE c1 CURSOR READ_ONLY
or
DECLARE c1 CURSOR FAST_FORWARD READ_ONLY
or
DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY

It depends. Do you need to move backwards as well as forward? Then you can't use forward_only or fast_forward. Do you need to update data in the cursor? Then you can't use read_only.

Cursors operate on each row, one at a time. SQL is a set based language designed to work on blocks of data. Which is faster, one thing at a time or lots of things at once?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Grant Fritchey (6/25/2009)
Cursors operate on each row, one at a time. SQL is a set based language designed to work on blocks of data. Which is faster, one thing at a time or lots of things at once?

An example I heard once.

You want to put some rice into a saucepan. Which is faster, scooping a cup of rice into the saucepan or moving the rice one grain at a time?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

Oooh. That's a good one.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Grant Fritchey (6/25/2009)
Oooh. That's a good one.

I'd give credit, but I have no recollection who initially said it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eswin SSCrazy Eights Points: 8320 More actions · Answer 9

Lynn Pettis (6/25/2009)
Have you read the articles that I asked you to read yet in my post above? If not, please go read them.

I know he is slow and a whole lot lazy.

But still while using it i wanted to know which is the better option.

Tanx 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Eswin (6/26/2009)
Lynn Pettis (6/25/2009)
Have you read the articles that I asked you to read yet in my post above? If not, please go read them.
I know he is slow and a whole lot lazy.
But still while using it i wanted to know which is the better option.

Best option? Okay, except for certain maitenance routines, you should avoid cursors as much as possible. There may be a use for them, but you should really document why you are using it and it should be the option of last choice.