SQL 遊標總結和效能分析(1)

Cursor Type

Work On Table

Rows retrieve

Scrolling Type

SQL Statement

Fast?

Read Only?

Client?

Server?

Open Cost

Fetche Cost

Scroll Overhaed

Impact Tempdb

Concurrency

Lock

Forward-Only

directly   On table

Until   FETCH

Only   FETCH NEXT

FAST_FORWARD

Fast

Yes

Yes

Yes

Low(not   tempdb)

Low

Low

Low

Static

Snapshot in Tempdb

After   open cursor

All   scrolling options

STATIC

Faster

Yes

Yes

YES

Highest

Lowest(create   in tempdb)

Highest

No   Lock

Keyset-driven

1.Controlled   by Keyset 2.After   open cursor,create the keyset of row in the tempdb 3.Limit   the membership

1.First   look at the keyset of rows in tempdb. 2.Navigate   to table to retrieve the data.

All   scrolling options

KEYSET

Slowly

No,all   changes

NO

Yes

lower   open cost than the static cursor; Higher   open cost than forward-only and dynamic cursors

Highest

lower   impact than the static cursor; higher   than forward and dymamic cursor

Higher   lock than the static cursor.

Dynamic

directly   On table

Until   FETCH

All   scrolling options,except BSOLUTE

DYNAMIC

Slowest

No,all   changes

NO

Yes

Low(not   tempdb) than static and keyset cursor

Highest   Lock

Cursor Type	Update?	Lock?	Fast?
Read Only	No	No   Locks	fastest
Optimistic	Yes	No   Locks	fast
Scroll Locks	Yes	U Lock	Slowest