🚀 go-pugleaf

Hi,

I spent some hours trying to find the optimal settings accessing a local
Access-Database using ADO and it seems there are no optimal settings. It
pretty much depends on what one intends to do...

Perhaps someone could comment on my findings or even point me to important
settings I might have missed.

Thanks
Eike

====================================================================
-- snip
====================================================================

TADOConnection
==============
ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\RTE\DAT\MDS\mds.mdb;Persist Security Info=False"
ConnectOptions=coConnectUnspecified
CursorLocation=clUseServer
IsolationLevel=ilCursorStability
Provider=Microsoft.Jet.OLEDB.4.0
KeepConnection=true
Mode=ReadWrite

TADOTable
=========
CacheSize=128
ReadOnly=false
LockType=ltOptimistic

I.) TableDirect = false
                                                           Benchmark                        Capabilities

#|CursorLocation | CursorType         |Loc1|Loc1R|Loc10|Ins1K|Upd100|Filter|ReadN|RdEof    Loc Cre Upd
=======================================================================================    === === ===
1 clUseServer     ctUnspecified         n.s   n.s   n.s   791    n.s   2500    10   380      n   y   n
2 clUseServer     ctOpenForwardOnly     n.s   n.s   n.s   791    n.s   2600    10   370      n   y   n
3 clUseServer     ctDynamic             n.s   n.s   n.s   861    n.s   2600    10   389      n   y   n
4 clUseServer     ctKeyset              971   990  9554  2234  57262   2700    89 20000      y   y   y
5 clUseServer     ctStatic              831   862  8433  2343  44424   2603    90 20000      y   y   y
6 clUseClient     ctUnspecified           A     A     A     A      A      A     A     A      A   A   A
7 clUseClient     ctOpenForwardOnly       A     A     A     A      A      A     A     A      A   A   A
8 clUseClient     ctDynamic               A     A     A     A      A      A     A     A      A   A   A
9 clUseClient     ctKeyset                A     A     A     A      A      A     A     A      A   A   A
A clUseClient     ctStatic              821    10    10 10025   1412     10    70 32000      y   y   y

II.) TableDirect = true

#|CursorLocation | CursorType         |Loc1|Loc1R|Loc10|Ins1K|Upd100|Filter|ReadN|RdEof    Loc Cre Upd
=======================================================================================    === === ===
1 clUseServer     ctUnspecified         n.s   n.s   n.s  1200    n.s    230    10   350      n   y   n
2 clUseServer     ctOpenForwardOnly     n.s   n.s   n.s  1500    n.s    230    10   340      n   y   n
3 clUseServer     ctDynamic             n.s   n.s   n.s  1200    n.s    240    10   320      n   y   n
4 clUseServer     ctKeyset               90    80  2200  2600  20000    230    90 20540      y   y   y
5 clUseServer     ctStatic               80    80  2200  2600  15000    230    80 20400      y   y   y
6 clUseClient     ctUnspecified           A     A     A     A      A      A     A     A      A   A   A
7 clUseClient     ctOpenForwardOnly       A     A     A     A      A      A     A     A      A   A   A
8 clUseClient     ctDynamic               A     A     A     A      A      A     A     A      A   A   A
9 clUseClient     ctKeyset                A     A     A     A      A      A     A     A      A   A   A
A clUseClient     ctStatic              841    10    10 11700   2800    120    70 31000      y   y   y

TADOCommand
===========
CursorLocation=clUseServer
CommandType=cmdText
ExecuteOptions=eoExecuteNoRecords

SQL-INSERT (1000 records) : 5100ms

Explanation:
=============
Loc1 = Locate record at end of table (using index)
Loc1R = Locate again (using index)
Loc10 = Locate 10 records (using index)
Ins1K = Insert 1000 records
Upd100 = Locate and Update 100 records (using index)
Filter = Apply Filter (on indexed string-field)
ReadN = Read 100 records (sequential)
ReadEof = Read from 'Bof' to 'Eof'

Loc = Locate possible
Cre = Create possible
Upd = Update possible

All times in miliseconds. 20000 records in the table while performing tests.