Why is Access 97 so much faster than VB6 when searching?
in [Visual Basic]
|
From: Helmut Meukel on 2 May 2010 07:05 "MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag news:m4eqt5ppmkvvcv7cbbe6fhnd2aghu2u6la(a)4ax.com... > > I'm already using rs.CursorType = adOpenForwardOnly and the text field > in question (in the Access 97 mdb) is indexed. However, such an index > is probably pretty useless for this project, because I am searching > for words *within* the text field, not the whole text. Probably the > text field's index would be useful if one were searching for the whole > field as a single phrase, but the index that Access builds when one > sets up an index on a text field isn't going to know about individual > words. > > MM This Index on the Textfield isn't just useless if you *never* search for the whole text, it may even degrade overall performance. Create a copy of the database, drop this index in the copy, compact the copy and rerun both tests against the copy of the database. Let us know what results you get. Helmut.
From: Nobody on 2 May 2010 08:21 Some database servers have full text search feature, so your query would be faster, like MS SQL, but it seems that you want to do it without a server. I am not sure if SQLite has that, but you may want to look into it anyway. MS Access DB is limited to 2 GB, while SQLite can go up to 32 TB. Olaf has a toolset to make SQLite easier to use from VB. http://en.wikipedia.org/wiki/MSSQL#Full_Text_Search_Service http://en.wikipedia.org/wiki/SQLite Olaf site: http://www.thecommon.net/3.html
From: Mike B on 2 May 2010 09:34 "Nobody" <nobody(a)nobody.com> wrote in message news:%23sciEIf6KHA.5808(a)TK2MSFTNGP02.phx.gbl... > Some database servers have full text search feature, so your query would > be faster, like MS SQL, but it seems that you want to do it without a > server. I am not sure if SQLite has that, but you may want to look into it > anyway. MS Access DB is limited to 2 GB, while SQLite can go up to 32 TB. > Olaf has a toolset to make SQLite easier to use from VB. 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. > http://en.wikipedia.org/wiki/MSSQL#Full_Text_Search_Service > > http://en.wikipedia.org/wiki/SQLite > > Olaf site: > http://www.thecommon.net/3.html > >
From: Nobody on 2 May 2010 10:03 "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.
From: MM on 2 May 2010 10:13
On Sun, 2 May 2010 13:05:20 +0200, "Helmut Meukel" <NoSpam(a)NoProvider.de> wrote: >"MM" <kylix_is(a)yahoo.co.uk> schrieb im Newsbeitrag >news:m4eqt5ppmkvvcv7cbbe6fhnd2aghu2u6la(a)4ax.com... >> >> I'm already using rs.CursorType = adOpenForwardOnly and the text field >> in question (in the Access 97 mdb) is indexed. However, such an index >> is probably pretty useless for this project, because I am searching >> for words *within* the text field, not the whole text. Probably the >> text field's index would be useful if one were searching for the whole >> field as a single phrase, but the index that Access builds when one >> sets up an index on a text field isn't going to know about individual >> words. >> >> MM > >This Index on the Textfield isn't just useless if you *never* search for >the whole text, it may even degrade overall performance. > >Create a copy of the database, drop this index in the copy, >compact the copy and rerun both tests against the copy of the >database. Let us know what results you get. 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 |