Why is Access 97 so much faster than VB6 when searching?
in [Visual Basic]
|
From: Helmut Meukel on 2 May 2010 17:32 "MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag news:4u1rt5hlun5pe5t68023fuv8gbimlb35uu(a)4ax.com... > Right. > > Test 1 - Test with and without an index on the text field: > No difference. > > Test 2 - Replace ADO with DAO: > This is definitely faster. However, the speed-up is only a few > seconds, plus one is much more restricted with DAO. 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. > > MM So DAO was slower than Access? Did yo use a parameter query already stored in the mdb? If you use Access with a SQL query, it preprocesses the query. If you use VB and DAO then the Jet engine can't use a preprocessed query and had to do all on-the-fly. Therfore I suggested to use a parameter query stored in the db and just pass the search criteria as the parameter value. It's a bit tricky to use the DataControl with a parameter query. IIRC, you have to create the recordset by code and then bind it to the DataControl. Sorry to be so vague, but I last used the DataControl about 13 years ago. I usually populate my Grids (and other controls) manually. I don't like the restrictions of bound controls. Helmut.
From: Schmidt on 2 May 2010 19:40 "MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag news:ddjrt59hsulj815n4qbvptvppvq53hmfev(a)4ax.com... > I've just unzipped dhRichClient3.zip into c:\dhRichClient3, > but dhRichClient3.dll won't register. RegSvr32 says: > "GetLastError returns 0x0000001f." Hmm, what OS are you using there ... (IIRC) Win98 yet? (since the newer toolset-versions will work properly only on W2K and above) I once made a version which was running on Win98 too (on "special request") - this was in May 2009, dealing with unicows.dll etc... but in the versions since August 2009 or so, I've removed the unicows-support again from the dhRichClient3.dll-Code. The wrapper-dll deals with a lot uf Unicode- and WinAPI___W (...) functions - too much effort for me in the newer versions, to ensure Win98-compatibility "forever". 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 And then ensure, that you call the slightly adapted register.bat for registration, which is working with a hardwired: c:\Windows\System\regsvr32 "c:\dhRichClient3\dhRichClient3.dll" register-call now... This way the also contained unicows.dll (which should be placed in c:\dhRichClient3\... too) is properly recognized during the registering-process. So, make sure - you replace everything you already have in your c:\dhRichClient3\... toolset-folder with the content of the above zip (don't forget the now contained unicows.dll) - then call the "special Win98-adapted" register.bat. Hope my (Win98-)guesses are correct - and that everything goes well with the registration of this older version on your machine. Just tested the code of the SQLite-Wordsearch-examples on a Win98-VM which contains this special Wrapper-Version - and everything worked as it should with this somewhat older Dll. (there were not that many *critical* bugs fixed between version 3.0.11 and the current 3.0.22 - so, even 3.0.11 was already a mature toolset-version - no need "to panic" because of that ... <g>) Olaf
From: ralph on 2 May 2010 19:45 On Sun, 02 May 2010 15:13:30 +0100, MM <kylix_is(a)yahoo.co.uk> wrote: > >Test 1 - Test with and without an index on the text field: >No difference. > I wouldn't expect Indexing a text field would make much difference for this type of query. >Test 2 - Replace ADO with DAO: >This is definitely faster. However, the speed-up is only a few >seconds, plus one is much more restricted with DAO. 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. > With the introduction of "a grid", it appears we being asked to chase a moving target. (Also a possible comparison between bounded and unbounded methods.) Just what is your problem domain? What Grid? DBGrid (SP3), Data Grid, "FLEXGrid", ... ? Just what are you trying to measure? Basically, if you are trying to match the performance of MSAccess (using a Query object and the intrinsic DataView) with bolt-together controls and libraries in VB - you are probably going to be disappointed. In fact you would even notice a difference between a MSAccess Project (using VBA and Forums) and using the MSAccess data application directly; small but there. So what do you want? The fastest possible VB solution using out-of-the box tools? A faster database? A faster presentation? What has to stay? What can you adapt to? -ralph
From: MM on 3 May 2010 03:03 On Mon, 3 May 2010 01:40:17 +0200, "Schmidt" <sss(a)online.de> wrote: > >"MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag >news:ddjrt59hsulj815n4qbvptvppvq53hmfev(a)4ax.com... > >> I've just unzipped dhRichClient3.zip into c:\dhRichClient3, >> but dhRichClient3.dll won't register. RegSvr32 says: >> "GetLastError returns 0x0000001f." > >Hmm, what OS are you using there ... (IIRC) Win98 yet? Yep! >(since the newer toolset-versions will work properly only > on W2K and above) Oh, well... > >I once made a version which was running on Win98 too >(on "special request") - this was in May 2009, dealing >with unicows.dll etc... but in the versions since August >2009 or so, I've removed the unicows-support again >from the dhRichClient3.dll-Code. > >The wrapper-dll deals with a lot uf Unicode- and >WinAPI___W (...) functions - too much effort for me >in the newer versions, to ensure Win98-compatibility >"forever". > >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 > >And then ensure, that you call the slightly adapted >register.bat for registration, which is working with >a hardwired: >c:\Windows\System\regsvr32 "c:\dhRichClient3\dhRichClient3.dll" >register-call now... This way the also contained unicows.dll >(which should be placed in c:\dhRichClient3\... too) is properly >recognized during the registering-process. > >So, make sure - you replace everything you already have in >your c:\dhRichClient3\... toolset-folder with the content of the >above zip (don't forget the now contained unicows.dll) - >then call the "special Win98-adapted" register.bat. > >Hope my (Win98-)guesses are correct - and that everything goes >well with the registration of this older version on your machine. >Just tested the code of the SQLite-Wordsearch-examples >on a Win98-VM which contains this special Wrapper-Version - >and everything worked as it should with this somewhat older Dll. >(there were not that many *critical* bugs fixed between > version 3.0.11 and the current 3.0.22 - so, even 3.0.11 > was already a mature toolset-version - no need "to panic" > because of that ... <g>) > >Olaf Thanks. I'll give it a try and report back. MM
From: MM on 3 May 2010 03:13
On Sun, 02 May 2010 18:45:45 -0500, ralph <nt_consulting64(a)yahoo.net> wrote: >On Sun, 02 May 2010 15:13:30 +0100, MM <kylix_is(a)yahoo.co.uk> wrote: > >> >>Test 1 - Test with and without an index on the text field: >>No difference. >> > >I wouldn't expect Indexing a text field would make much difference for >this type of query. > >>Test 2 - Replace ADO with DAO: >>This is definitely faster. However, the speed-up is only a few >>seconds, plus one is much more restricted with DAO. 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. >> > >With the introduction of "a grid", it appears we being asked to chase >a moving target. (Also a possible comparison between bounded and >unbounded methods.) I've alwasy had the grid. Perhaps I didn't make this clear originally. What I had was this: An Access 97 mdb, a VB6 app, an MSHFlexGrid. I ran a simple SELECT query in Access (created a new query in the Access GUI) and timed it. It took approx 15 secs to fill the Access-provided grid. I copied the SQL string and ran the same query via VB6 and it took approx 43 secs to fill the MSHFlexGrid. This is why I said that VB6 takes three times longer. >Just what is your problem domain? What Grid? DBGrid (SP3), Data Grid, >"FLEXGrid", ... ? MSHFlexGrid > >Just what are you trying to measure? The time difference between running the query within Access and running it from VB6. >Basically, if you are trying to match the performance of MSAccess >(using a Query object and the intrinsic DataView) with bolt-together >controls and libraries in VB - you are probably going to be >disappointed. That is correct. Not so much disappointed as surprised. >In fact you would even notice a difference between a MSAccess Project >(using VBA and Forums) and using the MSAccess data application >directly; small but there. > >So what do you want? The fastest possible VB solution using out-of-the >box tools? A faster database? A faster presentation? 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. MM |