Why is Access 97 so much faster than VB6 when searching?
in [Visual Basic]
|
From: MM on 3 May 2010 05:28 On Mon, 3 May 2010 01:40:17 +0200, "Schmidt" <sss(a)online.de> wrote: >So, if that is the case (a Win98-OS) - please download this >somewhat older RichClient3-Version (3.0.11), which I've just >uploaded here: >www.datenhaus.de/Downloads/dhRichClient3_Win98.zip Right, I've now done this. In ADODAOTest I had to replace the references to 2.8, since I don't have that on this machine. Instead I've used - Microsoft ActiveX Data Objects 2.5 Library - Microsoft ADO Ext. 2.5 for DDL and Security Anyway, it worked and these are the results when I 'Click Me': 1st Pass ADO: 100 2.469453 DAO: 100 2.140703 2nd Pass ADO: 100 1.648125 DAO: 100 1.429688 Next, the SQLite test: 1st Pass: SQLite, normal Like-Search: 100 0.331875 SQLite. FTS3-Search: 101 -1.5625E -04 2nd Pass: SQLite, normal Like-Search: 100 0.3296875 SQLite. FTS3-Search: 101 0.0015625 The SQLite results are amazing. So I shall take a further look at SQLite, especially now that I have proved it works with Windows 98 with your dhRichClient3_Win98.zip stuff. Many thanks for now. Very impressive (as always!) MM
From: Schmidt on 3 May 2010 06:57 "MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag news:7e5tt51cn4k5ki1c2k6ck3risvvtg5f2qt(a)4ax.com... > Anyway, it worked and these are the results when I 'Click Me': > > 1st Pass > ADO: 100 2.469453 > DAO: 100 2.140703 > > 2nd Pass > ADO: 100 1.648125 > DAO: 100 1.429688 > > > Next, the SQLite test: > > 1st Pass: > SQLite, normal Like-Search: 100 0.331875 > SQLite. FTS3-Search: 101 -1.5625E -04 > > 2nd Pass: > SQLite, normal Like-Search: 100 0.3296875 > SQLite. FTS3-Search: 101 0.0015625 > > The SQLite results are amazing. So I shall take a > further look at SQLite, especially now that I have > proved it works with Windows 98 > with your dhRichClient3_Win98.zip stuff. Ah, glad it works - and the result-relations match with my timings here too. :-) If you want to dive into the SQLite-wrapper, its usage is (as said) not all that much different from ADO/DAO - I've tried to mimick the ADO-Connection and Recordset- Objects in a pretty compatible way. There are enhanced examples downloadable in a Demo-Zip-Package on: www.thecommon.net/3.html The Demo-Package includes a SubFolder, named 'SQLite' - and within that Folder is a SubFolder, called 'NWind-Demo'. The NWind-VB-Project imports the wellknown NWind.mdb database "on the fly" (on startup) into an SQLite NWind.db-File and then offers many comparison-demos - as well as "special SQLite-stuff" in different (relative small code) SubForms. So, there are already Import-Classes contained in this NWind- Demo, which should allow you, to copy over your own *.mdb into an SQLite-DB (but maybe the easiest way is, to just rename your 'TextContent.mdb' to 'NWind.mdb' temporarily - and place that within the NWind-Demo-Folder instead of the "real" NWind.mdb - and then press the convert-Button there - aside from a few error-messageboxes (regarding non-applyable NWind-View-Definitions) the table-conversion should take place nonetheless. Oh, and DataGrids (as the VB6-DataGrid or the MSHFlex) can be bound to these SQLite-Recordsets (with one line of code) too - just look within the just mentioned Demo, on how this is done (the Demo binds the SQLite-Rs to a VB6- DataGrid). Would be interested to hear about some results, as soon as you got your TextDB-Data copied over into an SQLiteDB-File. ;-) Olaf
From: MM on 3 May 2010 08:31 On Mon, 3 May 2010 12:57:08 +0200, "Schmidt" <sss(a)online.de> wrote: > >"MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag >news:7e5tt51cn4k5ki1c2k6ck3risvvtg5f2qt(a)4ax.com... > >> Anyway, it worked and these are the results when I 'Click Me': >> >> 1st Pass >> ADO: 100 2.469453 >> DAO: 100 2.140703 >> >> 2nd Pass >> ADO: 100 1.648125 >> DAO: 100 1.429688 >> >> >> Next, the SQLite test: >> >> 1st Pass: >> SQLite, normal Like-Search: 100 0.331875 >> SQLite. FTS3-Search: 101 -1.5625E -04 >> >> 2nd Pass: >> SQLite, normal Like-Search: 100 0.3296875 >> SQLite. FTS3-Search: 101 0.0015625 >> >> The SQLite results are amazing. So I shall take a >> further look at SQLite, especially now that I have >> proved it works with Windows 98 >> with your dhRichClient3_Win98.zip stuff. > >Ah, glad it works - and the result-relations match with >my timings here too. :-) > >If you want to dive into the SQLite-wrapper, its usage >is (as said) not all that much different from ADO/DAO - >I've tried to mimick the ADO-Connection and Recordset- >Objects in a pretty compatible way. > >There are enhanced examples downloadable in a >Demo-Zip-Package on: >www.thecommon.net/3.html >The Demo-Package includes a SubFolder, named 'SQLite' - >and within that Folder is a SubFolder, called 'NWind-Demo'. >The NWind-VB-Project imports the wellknown NWind.mdb >database "on the fly" (on startup) into an SQLite NWind.db-File >and then offers many comparison-demos - as well as "special >SQLite-stuff" in different (relative small code) SubForms. > >So, there are already Import-Classes contained in this NWind- >Demo, which should allow you, to copy over your own *.mdb >into an SQLite-DB (but maybe the easiest way is, to just rename >your 'TextContent.mdb' to 'NWind.mdb' temporarily - and >place that within the NWind-Demo-Folder instead of the >"real" NWind.mdb - and then press the convert-Button there - >aside from a few error-messageboxes (regarding non-applyable >NWind-View-Definitions) the table-conversion should take place >nonetheless. > >Oh, and DataGrids (as the VB6-DataGrid or the MSHFlex) >can be bound to these SQLite-Recordsets (with one line of >code) too - just look within the just mentioned Demo, on >how this is done (the Demo binds the SQLite-Rs to a VB6- >DataGrid). > >Would be interested to hear about some results, as soon as >you got your TextDB-Data copied over into an SQLiteDB-File. >;-) > >Olaf > Yes, I'll keep you posted. Just a quick question right now: In the line Set RsFTS3 = Cnn.OpenRecordset("Select * From Test_FTSearch Where Txt Match '(Ford AND Pref*) OR 199999'") 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? MM
From: Paul Clement on 3 May 2010 08:50 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. 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. Paul ~~~~ Microsoft MVP (Visual Basic)
From: MM on 3 May 2010 13:31
On Mon, 3 May 2010 12:57:08 +0200, "Schmidt" <sss(a)online.de> wrote: >Would be interested to hear about some results, as soon as >you got your TextDB-Data copied over into an SQLiteDB-File. Right, here's where I am at the moment. In the past few hours I have: - read as much as I can to get an overview of SQLite (at the SQLite home page) - downloaded sqlite-3_6_23_1.zip and extracted it to c:\program files\sqlite - downloaded Sqliteman v1.2.1 and installed it. - created a new db using Sqliteman, following the structure of an existing Access 97 database (obviously, not all the field types are equivalent) - imported a small Access 97 mdb via a CSV file. - successfully searched the resultant table in Sqliteman - deleted all the data, then imported a much larger Access mdb (approx 369mb) (same fields) This took approx 45 minutes to import. - again, successfully searched the resultant table. Much slower finding records based on % wildcards. Nota bene: I am NOT using the FTS3 extension yet! This is all still with queries using Like and % Problems: When I run Sqliteman on TextSearchTest.db3 I get the following error as Sqliteman loads and opens the "last database" (it always attempts to reload the last one used). "Error while getting the fileds of test_ftsearch: no such module: FTS3 Unable to execute statement." (I believe the word "fileds" in the above error is a typo and the author really means "fields".) Since Sqliteman, when installed, comes with its own copy of sqlite3.exe, I thought maybe that THAT version doesn't have the FTS3 extension in order for it to recognise the special kind of table. So I swapped that sqlite3.exe with the one from sqlite-3_6_23_1.zip, which is much bigger (517kb versus 360kb) This makes no difference. When I run Sqliteman it still generates the above error when opening TextSearchTest.db3 Note that even when I get the error, I can still execute the Select * From Test Where Txt Like '%Ford Prefect%' query. Just not the FTS3 query. When I attempt this I see in Sqliteman the following error: "Query Error: no such module: FTS3 Unable to execute statement" So I'm puzzled as to where the FTS3 stuff comes from. I looked several times on the SQlite website and read the fairly lengthy description of FTS3, but couldn't see any indication of *how* to actually get it! **** Maybe Sqliteman doesn't know about FTS3 at all, period. **** I assume that when I am accessing TextSearchTest.db3 with our little VB6 test program SQLiteTextSearch.vbp, this will be using dhRichClient3.dll which must somehow encapsulate FTS3. So my question right now is: how does one acquire FTS3 separately, to work with front-ends like Sqliteman? MM |