May 8, 2002 at 9:55 am
Does anyone out there really know how ADO's CursorType and CursorLocation works and affects performance with SQL Sever 2000 ? I have been trying to figure this out for about a week and I am more confused now than I was before I started.
Here are my questions :
1 - Server-side cursors (CursorLocation = adUseServer)
How SQL Server handles executing a query with a server side cursor depends on the CursorType :
adOpenForwardOnly : i think i get this one : the rows will be fetched and sent to the client one by one as needed (with Recordset forward Move methods). So if the query has a 10,000 row result and I stop reading at the 50th row - SQL Server will have only fetched and sent 50 rows to the client ? I think this the right cursor to use when passing thru an entire result set (reports, massive operations ...)
adOpenStatic : the entire result set is stored on the server in tempdb. So if the query has a 10,000 row result, will each and every row be fetched and stored in tempdb at the beginning or will the rows be fetched one at a time as needed (with Recordset Move methods) ? By the way, is tempdb memory or disk based ? This also means that any changes made to the data after this will not be visible (since every thing has been copied into tempdb) ? Does anyone out there actually use static cursors ? This seems crazy to me.
adOpenKeySet : the entire keyset is stored on the server in tempdb. What is a keyset ? So if the query has a 10,000 row result, will each and every key (?) be fetched and stored in tempdb at the beginning or will the they be fetched one at a time as needed (with Recordset Move methods) ? Each row that I request will then fetch the actual fields in the row and send them to the client ?
adOpenDynamic : rows will be fetched one at a time as needed (with Recordset Move methods) .
So if the query has a 10,000 row result and I stop reading at the 50th row – SQL Server will have only fetched and sent 50 rows to the client.
Does SQL Sever support bookmarks in this mode ? Our software is based heavily on grids used to scroll and select records – all the data bound grids out there seem to require the use of either client-side cursors or server-side cursors (static and keyset but not dynamic). Our database is huge with over 1200 tables with some of them in the millions of records. It seems ridiculous to me to use any mode that requires fetching the entire result set ahead of time. Given this, how can i use a grid with the dynamic mode since, in my opionion, the is the only usable mode ?
2 - Client-side cursors (CursorLocation = adUseClient)
Executing a query (which builds a recordset) with a client side cursor forces SQL Server to read and send the entire result set to the client. So if the query has a 10,000 row result - each and every row will be fetched and stored in tempdb (?) and then sent thru the network to the client where it will be stored in the Recordset on the clients machine.
So, logically, the cursor types adOpenForwardOnly, adOpenKeySet and adOpenDynamic don’t make sense with client-side cursors ? Since SQL Server doesn’t give an error when using these cursor types, I am assuming that it simply ignores it and uses adOpenStatic ? Also, is the data in the clients Recordset stored in memory or on disk or a combination of both ?
Some of you out there might realize from my questions that I come from a navigational background instead of relational (good old GetEqual, GetGreater, GetFirst… commands). Using navigational commands everything is in the programmers control – read and write the least amount of data – now we are the mercy of the server …. Ah the good old days !!!
Thanks in advance to anyone who takes the time to respond to my questions !
May 8, 2002 at 11:06 am
Dont have time at the momemt to answer in detail, but here are a couple pieces. With a client side cursor its always static regardless of what you set (and no error is generated), and always brings ALL the data to the client. Server cursors do put a heavier load on the server - without digging into the details anything besides streaming the data out has to take more work.
Bill Vaughn has some excellent books out about this subject.
Andy
May 11, 2002 at 6:37 pm
hI,
I am new at this too. This is from VB6 help code.
Public Sub SupportsX()
Dim aintCursorType(4) As Integer
Dim rs As ADODB.Recordset
Dim strCnn As String
Dim intIndex As Integer
' Open connections.
' strCnn = "Provider=sqloledb;" & _
' "Data Source=link99\link;Initial Catalog=Issue;User Id=sa;Password=; "
' Fill array with CursorType constants.
aintCursorType(0) = adOpenForwardOnly
aintCursorType(1) = adOpenKeyset
aintCursorType(2) = adOpenDynamic
aintCursorType(3) = adOpenStatic
' Open recordset using each CursorType and
' optimistic locking. Then call the DisplaySupport
' procedure to display the supported options.
Debug.Print "Recordset conn.Execute(usp_RmaMove 'First', & SeqID)"
For intIndex = 0 To 3
Set rs = New ADODB.Recordset
rs.CursorType = aintCursorType(intIndex)
rs.LockType = adLockOptimistic
Set rs = conn.Execute("Exec usp_RmaMove 'First', " & SeqID)
' rs.Open "t_RmaNo", strCnn, , , adCmdTable
' Set rs = conn.Execute("ViewfrmSalesSelectIssue ")
' rs.Open "ViewfrmSalesSelectIssue", strCnn, , , adCmdTable
Select Case aintCursorType(intIndex)
Case adOpenForwardOnly
Debug.Print "ForwardOnly cursor supports:"
Case adOpenKeyset
Debug.Print "Keyset cursor supports:"
Case adOpenDynamic
Debug.Print "Dynamic cursor supports:"
Case adOpenStatic
Debug.Print "Static cursor supports:"
End Select
DisplaySupport rs
rs.Close
Next intIndex
End Sub
Public Sub DisplaySupport(rstTemp As ADODB.Recordset)
Dim alngConstants(11) As Long
Dim booSupports As Boolean
Dim intIndex As Integer
' Fill array with cursor option constants.
alngConstants(0) = adAddNew
alngConstants(1) = adApproxPosition
alngConstants(2) = adBookmark
alngConstants(3) = adDelete
alngConstants(4) = adFind
alngConstants(5) = adHoldRecords
alngConstants(6) = adMovePrevious
alngConstants(7) = adNotify
alngConstants(8) = adResync
alngConstants(9) = adUpdate
alngConstants(10) = adUpdateBatch
For intIndex = 0 To 10
booSupports = _
rstTemp.Supports(alngConstants(intIndex))
If booSupports Then
Select Case alngConstants(intIndex)
Case adAddNew
Debug.Print " AddNew"
Case adApproxPosition
Debug.Print " AbsolutePosition and AbsolutePage"
Case adBookmark
Debug.Print " Bookmark"
Case adDelete
Debug.Print " Delete"
Case adFind
Debug.Print " Find"
Case adHoldRecords
Debug.Print " Holding Records"
Case adMovePrevious
Debug.Print " MovePrevious and Move"
Case adNotify
Debug.Print " Notifications"
Case adResync
Debug.Print " Resyncing data"
Case adUpdate
Debug.Print " Update"
Case adUpdateBatch
Debug.Print " batch updating"
End Select
End If
Next intIndex
End Sub
Sub ProcedureParameters()
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
' Dim cat As New ADOX.Catalog ' What fucking library?
' Open the catalog
' Set cat.ActiveConnection = conn
' Get the command object usp_update_t_RmaItem
' Set cmd = cat.Procedures("CustomerById").Command
' Fucked up with SQL Server
' Set cmd = cat.Procedures("usp_updateRmaItem").Command
' Retrieve Parameter information
' cmd.Parameters.Refresh
For Each prm In cmd.Parameters
Debug.Print prm.Name & ":" & prm.Type
Next
End Sub
This gives an output of:
Recordset conn.Execute(usp_RmaMove 'First', & SeqID)
ForwardOnly cursor supports:
AbsolutePosition and AbsolutePage
Bookmark
Find
Holding Records
MovePrevious and Move
Notifications
Resyncing data
Keyset cursor supports:
AbsolutePosition and AbsolutePage
Bookmark
Find
Holding Records
MovePrevious and Move
Notifications
Resyncing data
Dynamic cursor supports:
AbsolutePosition and AbsolutePage
Bookmark
Find
Holding Records
MovePrevious and Move
Notifications
Resyncing data
Static cursor supports:
AbsolutePosition and AbsolutePage
Bookmark
Find
Holding Records
MovePrevious and Move
Notifications
Resyncing data
Use your own stored procedure.
Hope this helps
Link
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply