Cursors

1
Cursors Application Side and Transact-SQL

• Tony Rogerson SQL Server MVP
• Torver Computer Consultants

2
The presentation

• Accessing Data using ADO and ADO.NET
• Performance Differences
• Different ADO cursor types
• ADO.NET SQLDataRead and SQLDataAdapter
• Transact-SQL
• Options on DECLARE CURSOR and their performance.

3
Data Access

• Primarily, especially for UI, cursors are never
  used in a scroll capacity single pass.
• For DP do all rows get updated (if not consider
  calling sp to fire updated instead of an
  updateable cursor? Can a Set based statement not
  accomplish what you want?
• I will talk primarily about UI as thats where
  the scalability problems come in (multiple
  concurrent users).

4
ADO.NET

• Cursors are not exposed through the object model.
• Dataset offer a client-side like cursor
  functionality which you can move through
  independently of the database. Populate through
  SQLDataAdapter.
• SQLReader is blindingly quick for sucking data
  into your app (like a firehose single pass).
• You need SQLDataAdapter for some things eg. in
  datalist.ItemDataBind to get at the data row
  Ctype(e.Item.DataItem, DataRowView)

5
Demo

• ASP.NET application connecting to local SQL
  Server difference between SQLDataReader and
  SQLDataAdapter.

6
ADO

• Default is server-side, forward only.
• CacheSize is meaningless for forward only
  doesnt make a difference.
• Use CacheSize to enhance performance for all
  other cursor types.
• Always try and use Forward-only.

7
Demo

• VB application using ADO (2.7).
• Check SQL Profiler

8
ADO CacheSize

• Default is 1 row
• Performance is terrible with 1 row!
• Try 100 as a starting point.
• Best value is dependant on your application and
  the network etc

rsTest New ADODB.Recordset . rsTest.CacheSize
100 rsTest.Open
9
Transact SQL Cursor

• Default is FORWARD_ONLY
• Single Pass
• Depending on what other options are specified, is
  NOT read only.
• FAST_FORWARD
• Single Pass Read Only Scroll functions not
  accessible.
• STATIC
• -Scrollable Read Only

10
Best Cursor Type

• STATIC comes out on top (seemingly)
• Consider tempdb usage though.
• Decision (in my mind) is still out between
  FAST_FORWARD and STATIC.
• Benchmark!

11
Questions