Why is Access 97 so much faster than VB6 when searching?
in [Visual Basic]
|
From: ralph on 3 May 2010 13:55 On Mon, 03 May 2010 07:50:29 -0500, Paul Clement <UseAdddressAtEndofMessage(a)swspectrum.com> wrote: >On Sun, 02 May 2010 01:20:20 GMT, sfdavidkaye2(a)yahoo.com (David Kaye) wrote: > >� ADO versus DAO. ADO is an "out of process" connection, meaning that a >� communication link is established between it and your VB6 program. DAO runs >� within VB6, so it has a lot less overhead. Use DAO and you should see similar >� speed. Also, don't forget to use the forward-only option when you can, and be >� sure to index the keys you'll be using in your SQLs. > >Both ADO and DAO run from within the same host process. There is no difference between the two in >this respect. > lol I always expect someone to bring this up - whenever the terms "out-of-proc" and "in-proc" are mentioned. Once upon a time the distinction was very clear, but recently the boundries have blurred considerably. Technically you are correct. But you error if you are suggesting that how objects/instances and the communication between them is managed is not different because they are technically both running within a "host process", or that those differences don't impact performance. >The simple explanation is that DAO was designed specifically to operate with Access databases while >ADO was designed to be more flexible and support many databases through an intermediate data access >layer called an OLEDB Provider. As a result, with ADO there is additional overhead involved with >handling database sessions and working with other objects that are not optimized for Jet databases. > Actually, DAO was designed to an independent data access library supporting Open Database Connectivity (ODBC), ISAM, and Jet* - which by the way also is designed around an "intermediate data access layer" - called "drivers". One can easily confuse DAO with "Access" simply because - "they basically had to tear it out of Access <'jet'> and graft it onto VB" (Tony Goodhew). However, it later became a data access library in its own right. -ralph
From: ralph on 3 May 2010 14:05 On Mon, 03 May 2010 08:13:50 +0100, MM <kylix_is(a)yahoo.co.uk> wrote: > >I want a solution, preferably in VB6, that will search a text field on >individual words and return the list of hits within 3 seconds. The >database contains 1.7 million records. > I perhaps sounded harsher than I meant. This definitely adds clarity for your to your requrements. Mostly likely you need to investigate an alternative data storage and a more stream-lined presentation widget. Perhaps your own 'storage' and your own 'grid'. -ralph
From: MM on 3 May 2010 14:37 On Mon, 03 May 2010 13:05:01 -0500, ralph <nt_consulting64(a)yahoo.net> wrote: >On Mon, 03 May 2010 08:13:50 +0100, MM <kylix_is(a)yahoo.co.uk> wrote: > > >> >>I want a solution, preferably in VB6, that will search a text field on >>individual words and return the list of hits within 3 seconds. The >>database contains 1.7 million records. >> > >I perhaps sounded harsher than I meant. > >This definitely adds clarity for your to your requrements. > >Mostly likely you need to investigate an alternative data storage and >a more stream-lined presentation widget. Perhaps your own 'storage' >and your own 'grid'. Back in the 1980s I remember a product called BTrieve and it still seems to be around, albeit there is now an upgrade path to something called Pervasive PSQL. I have in the past always been amazed at how fast a binary chop approach can be if one has an ordered set of unique keys, even on large numbers of them, though I don't know which would be faster over a million or two million records: a binary chop using VB's Seek and Get, or using an Access mdb with just two tables - the keys and the pointers. (Actually, it wouldn't be a million keys, since only unique words would be indexed. It would be a lot less.) But anyway all this is now on the back burner since Olaf introduced me to the Full-Text Search 3 functionality in SQlite. I'm just having a slight problem at the moment finding a front-end that recognises FTS3. (Since I'm not about to go to a DOS command window and interact with Sqlite in that way!) MM
From: Paul Clement on 4 May 2010 11:00 On Mon, 03 May 2010 12:55:51 -0500, ralph <nt_consulting64(a)yahoo.net> wrote: � >Both ADO and DAO run from within the same host process. There is no difference between the two in � >this respect. � > � � lol � � I always expect someone to bring this up - whenever the terms � "out-of-proc" and "in-proc" are mentioned. Once upon a time the � distinction was very clear, but recently the boundries have blurred � considerably. Technically you are correct. � � But you error if you are suggesting that how objects/instances and the � communication between them is managed is not different because they � are technically both running within a "host process", or that those � differences don't impact performance. My suggestion was that it isn't relevant in this scenario as ADO also runs in-process with respect to the application. It's within the implementation of each library (or libraries) where the performance difference exists. � >The simple explanation is that DAO was designed specifically to operate with Access databases while � >ADO was designed to be more flexible and support many databases through an intermediate data access � >layer called an OLEDB Provider. As a result, with ADO there is additional overhead involved with � >handling database sessions and working with other objects that are not optimized for Jet databases. � > � � Actually, DAO was designed to an independent data access library � supporting Open Database Connectivity (ODBC), ISAM, and Jet* - which � by the way also is designed around an "intermediate data access layer" � - called "drivers". In DAO there is no intermediate data access layer with respect to native operations. There is, as you state, support for additional data access layers such as ODBC and ISAM drivers. � One can easily confuse DAO with "Access" simply because - "they � basically had to tear it out of Access <'jet'> and graft it onto VB" � (Tony Goodhew). However, it later became a data access library in its � own right. Exactly. Paul ~~~~ Microsoft MVP (Visual Basic)
From: Schmidt on 5 May 2010 19:27
"MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag news:c4gtt556pa2t07m33abpuec6cqslm3arje(a)4ax.com... > > I note that a wildcard * in place of the 'F' in Ford won't work. > > Thus: > > Set RsFTS3 = Cnn.OpenRecordset("Select * From Test_FTSearch > Where Txt Match '(*ord AND Pref*) OR 199999'") > > returns zero records. > > Is it possible to use Match, or some other operator, with wildcards > at both ends of the search string? No, FTS3 is a "Word-search", wich heavily relies on pre-indexed content. The recently introduced "short-circuiting" with a wildcard at the right-hand-side is only a nice addition, which was not taking all that much effort to build in, since it can make use of the existing Word-Index (a Binary-Chop - or BTree-search works well, even when only the LeftChars of a given word are passed into). That's basically the same thing (reason), why a Like-Search in this form here: ....Where TxtColumn Like 'SomeStr%' is able to make use of an index (if there is one defined on TxtColumn) but the forms: ....Where TxtColumn Like '%omeStr%' or ....Where TxtColumn Like '%omeString' would both need to fallback to a full-table-scan, since in these cases "nobody" knows, with which "most-left-char" to jump into an index. Olaf |