Why is Access 97 so much faster than VB6 when searching?
in [Visual Basic]
|
From: MM on 2 May 2010 10:15 On Sun, 2 May 2010 10:03:30 -0400, "Nobody" <nobody(a)nobody.com> wrote: >"Mike B" <mDotByerley(a)VerizonDottieNettie> wrote in message >news:u0t%237wf6KHA.1888(a)TK2MSFTNGP05.phx.gbl... >> I have used MS Sql and still use Access on a remote web server, but for >> all my critical stuff, I continue (as I have since 1986), venerable, >> formidable, dependable RBase (www.rbase.com) which can go up to 23,000,000 >> TB. > >RBase is not free, and I don't know if it requires installation. SQLite on >the other hand doesn't require installation or registration. It's a simple >DLL that you put in the same folder as the EXE. It's suitable when you don't >need a server, but you need some sort of DB, and I think I have heard that >it's faster than MS Access. I've been looking at SQLite since it first came up in this thread, and I might give it a whirl later. Anyone got an idiot's guide for SQLite virgins? (using it with VB6) MM
From: Schmidt on 2 May 2010 10:23 "MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag news:068pt5p21c3dn8tn3cvqk5sgf05o25raoc(a)4ax.com... [fast Word-lookups on DB-Tables] > What I have already thought of doing (I've been pondering > speed-up approaches for days) is this: > > - Split each subject into words. > - Discard stop words, hyphens, punctuation etc. > - Add each word to a database (doesn't have to be Access). > Each word is added once only (acting as unique key).... > .... As already written in my other reply (and in the meantime also by 'Nobody')... The "word-indexing-approach" you describe above is already completely encapsulated (and that in a *very* efficient and optimized manner) by the Full-Text-Search implementations, many DB-Engines have to offer. This does not include JET, but e.g. SQLite (despite its small deployment-volume) has that built in. I've prepared a small VB-Demo, downloadable here: www.datenhaus.de/Downloads/DBTextSearch.zip ....which contains different comparisons (each of the Demos does create a DB with 200'000 entries in a Txt-Column on Startup once). The first folder contains an ADO vs. DAO-comparison, which both work against the same adLongVarWChar- Field, which contains the Text-Data being searched. The adLongVarWChar Field-Type stores its content (when driven by DAO 3.6 or JET 4.0) apparently as UTF8 - with no Text-Field "Lenght-Limit" - so this is comparable to a "Memo-Field" - and due to UTF8 encoding, the created Table-Content is not that large as e.g. for 16Bit encoded UTF16-FieldContent, which would be enforced e.g. by the adWChar Field-Type. The other Folder contains an SQLite vs SQLite comparison, where one Version is using a "Like %...%" search as in the ADO/DAO-Demo - but the second one is doing the same search against a second Table (containing a copy of all the Text-Content) using SQLites fulltext-search FTS3 (which BTW was sponsored to the SQLite-project - and thereby into the Public Domain by Google). Ok, here are some results from the Demos: DB-insert-performance (as said, done only once at startup for 200'000 records on a single Table, single Txt-Column): ADODAO *.mdb-creation: 98 seconds (DB-size: 13.8MB) SQLite *db3-creation: 8 seconds (DB-size: 22.5MB) A note to the SQLite results: The 8 seconds are already pretty fast, but this time should be cut in half for a fair comparison, since I've created two tables in that DB - one normal Table (for the Like- Search) - and one second FTS3-Table to show also the fulltext-search) - both Table Fillups need roughly only 4 seconds each - the second one (FTS3) doing even a Word-Indexing "on the fly" (under the hood, whilst normal TextField-Insertions take place over a Standard-Command-Object). The size of the SQLite-DB is therefore larger than the *.mdb, ...but when omitting the FTS3-Table, the DB- Size is only 8.5MB - a bit smaller than the Access-DB then. Search-Performance per Like-based "within-search" (the form which encloses the Searchstring within %...% and therefore always forces a FullTable-scan, since no index can be used in that mode). The string-content "ford prefect" was added at every 2000th record, so the resulting Rs always gives back 100 records. ADO: 0.95 seconds DAO: 0.82 seconds SQLite: 0.21 seconds And per SQLite FTS3: < 15 miliseconds, so this plays in an entirely different league... The searchterm used was: "... Where Txt Match '(Ford AND Pref*) Or 199999' just to demonstrate, what combinations are possible with the logical Operators (also note the "ShortCut"-Operator at the end of the word-part Pref* - this feature was added to FTS3 only one year ago or so) The above search-term was delivering 101 Records in the resulting Recordset (the same 100 records as in the Like-Search approaches above, and one additional Record, since the Text-Content being searched also contained a "current RecordIndex StringPart". So, please play around with it - and make sure, you install the dhRichClient3-stuff from: www.datenhaus.de/Downloads/dhRichClient3.zip beforehand, if you have an interest in running the SQLite-Demos too. The coding for SQLite does not differ much from the DAO/ADO-examples, if you compare carefully - the very same Recordset-Looping - or Insertion-code-sequences can be used, but see yourself...;-) Olaf
From: Mike B on 2 May 2010 10:33 "Nobody" <nobody(a)nobody.com> wrote in message news:OiqxFBg6KHA.3656(a)TK2MSFTNGP06.phx.gbl... > "Mike B" <mDotByerley(a)VerizonDottieNettie> wrote in message > news:u0t%237wf6KHA.1888(a)TK2MSFTNGP05.phx.gbl... >> I have used MS Sql and still use Access on a remote web server, but for >> all my critical stuff, I continue (as I have since 1986), venerable, >> formidable, dependable RBase (www.rbase.com) which can go up to >> 23,000,000 TB. > > RBase is not free, and I don't know if it requires installation. SQLite on > the other hand doesn't require installation or registration. It's a simple > DLL that you put in the same folder as the EXE. It's suitable when you > don't need a server, but you need some sort of DB, and I think I have > heard that it's faster than MS Access. [OT] I erred on the size. 2,300,000 TB.... But yeah, it's really zippy is the speed department and yes, it is not a free DB product. It is continually being advanced in the GUI, largely through a close association between developers and the company. I have never found a company where a replicable bug is fixed in the next iteration of a software update (usually every couple months), and if it is an issue to your particular instance, the update is usually delivered directly to you within 24 / 48 hrs after submission. So the pluses outweigh the minuses for me when it comes to cost versus free when you are depending on the product to run your business. It has its own 4gl language and IDE. The GUI is done in Delphi and uses Delphi / Windows objects for a comprehensive environment to deliver a complete application in a truly RAD fashion. There is a separate product, Oterro, that is basically the DB Engine without the GUI for use with other front ends if the developer so chooses. I have used VB / Oterro combination, but that goes back about eight or so years. The DB is still widely used in many US Govt segments, including Navy, Air Force, Army, and FBI. I have remained attached to it because of its ease of use and multi-user dependability. I have never, and I repeat never had data corruption or loss with RBase. > >
From: MM on 2 May 2010 15:25 On Sun, 2 May 2010 16:23:55 +0200, "Schmidt" <sss(a)online.de> wrote: > >"MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag >news:068pt5p21c3dn8tn3cvqk5sgf05o25raoc(a)4ax.com... > >[fast Word-lookups on DB-Tables] >> What I have already thought of doing (I've been pondering >> speed-up approaches for days) is this: >> >> - Split each subject into words. >> - Discard stop words, hyphens, punctuation etc. >> - Add each word to a database (doesn't have to be Access). >> Each word is added once only (acting as unique key).... >> .... > >As already written in my other reply (and in the meantime >also by 'Nobody')... > >The "word-indexing-approach" you describe above is already >completely encapsulated (and that in a *very* efficient and >optimized manner) by the Full-Text-Search implementations, >many DB-Engines have to offer. This does not include JET, >but e.g. SQLite (despite its small deployment-volume) has >that built in. > >I've prepared a small VB-Demo, downloadable here: >www.datenhaus.de/Downloads/DBTextSearch.zip > >...which contains different comparisons (each of >the Demos does create a DB with 200'000 entries >in a Txt-Column on Startup once). > >The first folder contains an ADO vs. DAO-comparison, >which both work against the same adLongVarWChar- >Field, which contains the Text-Data being searched. >The adLongVarWChar Field-Type stores its content >(when driven by DAO 3.6 or JET 4.0) apparently as >UTF8 - with no Text-Field "Lenght-Limit" - so this >is comparable to a "Memo-Field" - and due to UTF8 >encoding, the created Table-Content is not that large >as e.g. for 16Bit encoded UTF16-FieldContent, which >would be enforced e.g. by the adWChar Field-Type. > >The other Folder contains an SQLite vs SQLite comparison, >where one Version is using a "Like %...%" search as in >the ADO/DAO-Demo - but the second one is doing the >same search against a second Table (containing a copy >of all the Text-Content) using SQLites fulltext-search FTS3 >(which BTW was sponsored to the SQLite-project - and > thereby into the Public Domain by Google). > >Ok, here are some results from the Demos: > >DB-insert-performance (as said, done only once at startup >for 200'000 records on a single Table, single Txt-Column): > >ADODAO *.mdb-creation: 98 seconds (DB-size: 13.8MB) >SQLite *db3-creation: 8 seconds (DB-size: 22.5MB) > >A note to the SQLite results: >The 8 seconds are already pretty fast, but this time should >be cut in half for a fair comparison, since I've created >two tables in that DB - one normal Table (for the Like- >Search) - and one second FTS3-Table to show also >the fulltext-search) - both Table Fillups need roughly >only 4 seconds each - the second one (FTS3) doing >even a Word-Indexing "on the fly" (under the hood, >whilst normal TextField-Insertions take place over a >Standard-Command-Object). >The size of the SQLite-DB is therefore larger than >the *.mdb, ...but when omitting the FTS3-Table, the DB- >Size is only 8.5MB - a bit smaller than the Access-DB then. > >Search-Performance per Like-based "within-search" >(the form which encloses the Searchstring within %...% > and therefore always forces a FullTable-scan, since > no index can be used in that mode). > >The string-content "ford prefect" was added at every 2000th >record, so the resulting Rs always gives back 100 records. > >ADO: 0.95 seconds >DAO: 0.82 seconds > >SQLite: 0.21 seconds > >And per SQLite FTS3: < 15 miliseconds, so this plays >in an entirely different league... The searchterm used was: >"... Where Txt Match '(Ford AND Pref*) Or 199999' >just to demonstrate, what combinations are possible >with the logical Operators (also note the "ShortCut"-Operator >at the end of the word-part Pref* - this feature was added >to FTS3 only one year ago or so) >The above search-term was delivering 101 Records >in the resulting Recordset (the same 100 records as in >the Like-Search approaches above, and one additional >Record, since the Text-Content being searched also >contained a "current RecordIndex StringPart". > >So, please play around with it - and make sure, you >install the dhRichClient3-stuff from: >www.datenhaus.de/Downloads/dhRichClient3.zip >beforehand, if you have an interest in running the >SQLite-Demos too. The coding for SQLite does not >differ much from the DAO/ADO-examples, if you >compare carefully - the very same Recordset-Looping - >or Insertion-code-sequences can be used, but see yourself...;-) > >Olaf I've just unzipped dhRichClient3.zip into c:\dhRichClient3, but dhRichClient3.dll won't register. RegSvr32 says: "GetLastError returns 0x0000001f." dhCairo.dll registered okay. MM
From: David Kaye on 2 May 2010 16:40
MM <kylix_is(a)yahoo.co.uk> wrote: >I had to expand my >brain for half an hour to recall when I last used DAO in 1998! It >seems I cannot connect a DAO RecordSet to a grid directly, as with >ADO, but must go via a Data control. I never connect controls to recordsets. I always do it manually. I've never liked the restrictions of making connections. |