Why is Access 97 so much faster than VB6 when searching?
in [Visual Basic]
|
From: MM on 1 May 2010 12:29 Details: Access 97 mdb, 1.7 million records. I'm searching a text field (indexed) for a given phrase. Access 97 takes 15 secs to execute: SELECT Data.DX_MsgNum, Data.DX_Subject, Data.DX_Date FROM Data WHERE (((Data.DX_Subject) Like "*ford prefect*")); However, in VB6 I have: Dim Conn As ADODB.Connection Dim rs As ADODB.Recordset Dim SQL as String Dim s as String s = "ford prefect" SQL = "SELECT DX_MsgNum, DX_Lines, DX_Date, " _ & "DX_Subject, DX_From FROM Data Where " _ & "DX_Subject like '%" & s & "%'" Set Conn = New ADODB.Connection OpenConnection Conn, DatabaseName Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.CursorType = adOpenForwardOnly rs.LockType = adLockReadOnly rs.Open SQL, Conn ********************************** This takes 43 secs! ********************************** Why does it take so much longer in VB6? Should I specify a different CursorLocation, CursorType and/or LockType? I'm reading William R Vaughan's "ADO Examples and Best Practices" at the moment, but haven't seen anything obvious yet that explains the considerable difference in performance. BTW, both tests are being run on the same PC. No network involved. MM
From: Nobody on 1 May 2010 14:50 "MM" <kylix_is(a)yahoo.co.uk> wrote in message news:j6lot5th85beksiedeqar3poeintdp3t2q(a)4ax.com... > Details: Access 97 mdb, 1.7 million records. I'm searching a text > field (indexed) for a given phrase. Access 97 takes 15 secs to > execute: > > SELECT Data.DX_MsgNum, Data.DX_Subject, Data.DX_Date > FROM Data > WHERE (((Data.DX_Subject) Like "*ford prefect*")); > > > However, in VB6 I have: > > Dim Conn As ADODB.Connection > Dim rs As ADODB.Recordset > Dim SQL as String > Dim s as String > > s = "ford prefect" > > SQL = "SELECT DX_MsgNum, DX_Lines, DX_Date, " _ > & "DX_Subject, DX_From FROM Data Where " _ > & "DX_Subject like '%" & s & "%'" > > Set Conn = New ADODB.Connection > OpenConnection Conn, DatabaseName > > Set rs = New ADODB.Recordset > rs.CursorLocation = adUseClient > rs.CursorType = adOpenForwardOnly > rs.LockType = adLockReadOnly > rs.Open SQL, Conn > > ********************************** > This takes 43 secs! > ********************************** > > Why does it take so much longer in VB6? Should I specify a different > CursorLocation, CursorType and/or LockType? I'm reading William R > Vaughan's "ADO Examples and Best Practices" at the moment, but haven't > seen anything obvious yet that explains the considerable difference in > performance. > > BTW, both tests are being run on the same PC. No network involved. Ralph answered this before. You are using ADO. DAO is older, but faster when used with JET databases because it's tightly optimized to it, so try using DAO.
From: ralph on 1 May 2010 15:00 On Sat, 01 May 2010 17:29:59 +0100, MM <kylix_is(a)yahoo.co.uk> wrote: >Details: Access 97 mdb, 1.7 million records. I'm searching a text >field (indexed) for a given phrase. Access 97 takes 15 secs to >execute: > >SELECT Data.DX_MsgNum, Data.DX_Subject, Data.DX_Date >FROM Data >WHERE (((Data.DX_Subject) Like "*ford prefect*")); > > >However, in VB6 I have: > >Dim Conn As ADODB.Connection >Dim rs As ADODB.Recordset >Dim SQL as String >Dim s as String > >s = "ford prefect" > >SQL = "SELECT DX_MsgNum, DX_Lines, DX_Date, " _ >& "DX_Subject, DX_From FROM Data Where " _ >& "DX_Subject like '%" & s & "%'" > >Set Conn = New ADODB.Connection >OpenConnection Conn, DatabaseName > >Set rs = New ADODB.Recordset >rs.CursorLocation = adUseClient >rs.CursorType = adOpenForwardOnly >rs.LockType = adLockReadOnly >rs.Open SQL, Conn > >********************************** >This takes 43 secs! >********************************** > >Why does it take so much longer in VB6? Should I specify a different >CursorLocation, CursorType and/or LockType? I'm reading William R >Vaughan's "ADO Examples and Best Practices" at the moment, but haven't >seen anything obvious yet that explains the considerable difference in >performance. > >BTW, both tests are being run on the same PC. No network involved. > >MM I suspect the first example, in "Access 97", is using DAO. Your VB6 application is using ADO. DAO is always faster than ADO with a local database. -ralph
From: Schmidt on 1 May 2010 15:12 "MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag news:j6lot5th85beksiedeqar3poeintdp3t2q(a)4ax.com... > Details: Access 97 mdb, 1.7 million records. I'm searching a text > field (indexed) for a given phrase. Access 97 takes 15 secs to > execute: > > SELECT Data.DX_MsgNum, Data.DX_Subject, Data.DX_Date > FROM Data > WHERE (((Data.DX_Subject) Like "*ford prefect*")); The reason why the same query per VB6+ADO takes roughly factor 2.5 longer is (IMO) due to Unicode-Conversion-stuff which takes place under the hood (whilst performing the "Contains-SubString"- operation per Like-Operator in the full-table-scan with the JET 4-engine). In the "15sec case" (if this is done directly within a running Access-Process) an probably older DAO-Version will be used, which is then able to make use of an ANSI-comparison-function against ANSI-Column- Content (without any special conversion-overhead). Normally one would think, that comparing longer 16Bit- Unicode-Strings (instead of 8-Bit-ANSI) would then take (not more than) factor 2 longer, but the now larger allocations for temporarily created Memory-Structures + the apparently needed ColumnFromAnsiToUnicode-conversions introduce (in sum) an overhead which comes near factor 3... Maybe you can reduce the query-times to only about factor 1.5 longer ones, if you convert the Access97-DB- Format to an *.mdb-format, which allows to define the Column-Field in question as varWChar, so that within the DB-File the Columns StringValues already reside as 16Bit wide ones - which would mean less conversion- overhead within the Like-comparisons themselfes. Another thought... If you want to reduce the time of such "find-words-within- column-content" to only miliseconds, then you could also make a try with a conversion of your *.mdb to an SQLite-DB, and there you would have Fulltext-Search-capabilities which do make use of a pre-indexing of the content in question over so called "Virtual Tables". After such preparations on your SQLite-DB you could then query the Content-Column in question in the same way as you are used to with a "Google- SearchField" (including AND, NOT and OR-operations for multiple SearchWords). If you have an interest in that, I could give you advise, how to achieve this goal with the RichClient3-stuff you perhaps already have in use (IIRC). Olaf
From: Schmidt on 1 May 2010 15:42
"ralph" <nt_consulting64(a)yahoo.net> schrieb im Newsbeitrag news:m9uot5tccbou117c6br28ftuahld03r6n6(a)4ax.com... > I suspect the first example, in "Access 97", is using DAO. > Your VB6 application is using ADO. > > DAO is always faster than ADO with a local database. Whilst the above is true in nearly all cases, MMs problem is not with the "different Recordset-creation and transfer- mechanisms" (which are different in DAO and ADO) - the resulting recordcount should be low in this case - and therefore less significant regarding performance. The "most time consuming loop" in this case is one layer below IMO, so this is more a "query-engine-thing" (JET-engine-Version related), and the query-engine always needs to scan the full table, to get access to the (about to be searched *within*) TextColumn - and if the TextColumn-Definition in the DB-Schema has forced a storage of the Columns content as ANSI-Text, then the function which finally performs the internal Like-comparison in the "table-scan-loop" would need to work in an ANSI- version too, to achieve an optimal throughput. If (for whatever reason) the "current JET-engine-version in use" does not choose different (maybe because only a "generalized Unicode" Like-comparison-function is available), optimal matching LikeW or LikeA implementations (according to the current storage-format of the scanned text-column), then the effect becomes explainable I'd say. But only guesses here on my side - we would get a clearer picture, if MM would create a "varWChar-Typed" additional Column in the DB (with a copy of the ANSI-ColumnContent) and then performs the same Like-query twice, once against the ANSI-column (as before), and then against the WChar-Column. Olaf |