<< Click to Display Table of Contents >> Navigation:  Examples > RecordSet > RecordSet 6: Cursor Types and performance

The sample below tried to show the differences between the various cursortypes, locktypes and cursor locations.

It also serves to show that you don't always get the type that you ask for: The YELLOW lines in the output show the differences!        

 

FUNCTION Sample1(nProv AS DWORD)   AS VOID

 LOCAL oRs AS AdoRecordSet

 LOCAL oConn AS AdoConnection

 LOCAL fSecs        AS FLOAT

 LOCAL x AS USUAL

 LOCAL i,j AS DWORD

 LOCAL nCurs, nType AS DWORD

 LOCAL aType        AS ARRAY

 LOCAL aCurs        AS ARRAY

 LOCAL nCount        AS DWORD

 LOCAL cConn AS STRING

 LOCAL nLoops        AS DWORD

 LOCAL cTable        AS STRING

 LOCAL nRealType AS LONG

 DO CASE

 CASE nProv == 1

 // SQL Server OleDb

 cConn := "Provider=SqlOleDB;Data Source=(local);"+;

 "Initial Catalog=Northwind;User Id=sa;Password=;"

 CASE nProv == 2

 // Jet through OleDb

 cConn := "Provider=Microsoft.Jet.OLEDB.4.0;" + ;

 "Data Source=C:\Vo2Ado26\Northwind.mdb;User Id=Admin;Password=;"        

 CASE nProv == 3

 // SQL Server through ODBC

 cConn := "Driver=Sql Server;Server=(local);Database=pubs;" + ;

 "User Id=sa;Password=;"        

 CASE nProv == 4

 // Jet through ODBC

 cConn := "Driver=Microsoft ACCESS Driver (*.mdb);"+;        "Fil=MsAccess;DBQ=C:\Vo2Ado26\Northwind.mdb;UId=Admin;Pwd=;"

 

 

 ENDCASE        

 nLoops := 5

 cTable := "orders"

 set color TO w+/b

 set alte TO LOG.txt

 set alte on

 cls

 ? "Testing cursor location, cursor types and lock types"

 ? cConn

 ? "Number OF loops", nLoops, "table ", cTable

 

 aCurs := {AdUseClient, AdUseServer}

 aType := {;

 {AdOpenForwardonly, AdLockReadonly},;

 {AdOpenForwardonly, AdLockOptimistic},;

 {AdOpenForwardonly, AdLockPessimistic},;

 {AdOpenStatic, AdLockReadonly},;

 {AdOpenStatic, AdLockOptimistic},;

 {AdOpenStatic, AdLockPessimistic},;

 {AdOpenKeySet, AdLockReadonly},;

 {AdOpenKeySet, AdLockOptimistic},;

 {AdOpenKeySet, AdLockPessimistic},;

 {AdOpenDynamic, AdLockReadonly},;

 {AdOpenDynamic, AdLockOptimistic},;

 {AdOpenDynamic, AdLockPessimistic}}

 

 oConn := AdoConnection{}

 oConn:Open(cConn,NIL,NIL,NIL)

 

 // first read a number of times to get data in the cache

 FOR i := 1 TO nLoops

 oRs := AdoRecordSet{}

 oRs:CursorLocation := AdUseServer

 oRs:Open(cTable,oConn,;

 AdOpenForwardOnly, AdLockReadOnly,AdCmdTable)

 oRs:Close()

 NEXT

 oConn:Close()

 // Swhitch between client and serverside

 FOR nCurs := 1 TO 2

 // various combinations of cursortype and locking

 oConn := AdoConnection{}

 oConn:CursorLocation := aCurs[nCurs]

 oConn:Open(cConn,NIL,NIL,NIL)

 FOR nType := 1 TO ALen(aType)

 fSecs := SECONDS()

 FOR i = 1 TO nLoops

 oRs := AdoRecordSet{}

 oRs:CursorLocation := aCurs[nCurs]

 oRs:Open(cTable,oConn,;

         aType[nType,1], aType[nType,2],AdCmdTable)

 nCount := 0

 DO WHILE ! oRs:EOF

         x := oRs:[Collect,1]

         x := oRs:[Collect,2]

         x := oRs:[Collect,3]

         oRs:MoveNext()

         ++nCount

 ENDDO

 nRealType := oRs:CursorType

 oRs:Close()

 NEXT

 IF nRealType=aType[nType,1]

 Set color TO W+/b

 ELSE

 Set color TO gr+/b

 ENDIF        

 ?  AdoEnum2Str(aCurs[nCurs], AdoCursorLocationEnum(),FALSE),;

 AdoEnum2Str(aType[nType,1], AdoCursorTypeEnum(),FALSE),;

 AdoEnum2Str(nRealType, AdoCursorTypeEnum(),FALSE),;

 AdoEnum2Str(aType[nType,2], AdoLockTypeEnum(),FALSE),;

 NTrim(SECONDS()-fSecs), "secs"

 

 NEXT

 oConn:Close()

 NEXT

 Set color TO W+/b

 wait

 RETURN