Reduced access times on a frontend/backend database

[Falcon101]

I have created a number of frontend/backend databases which are used to service users on a network. The Tables reside in the backend on the server and the front end is loaded locally on each machine. The backend is in Access97 to cater for various versions of Access being loaded on the front end machines. All later versions are happy to attach to the 97 tables. The backend contains only the tables, the front end contains all required queries and coding to perform the required functions.

Here is the problem. The more users that open the database (or at least their front end) the slower the database responds. I'm sure there must be a tweak to the refresh rate or some work around to at least maintain a respectable response time for all users, but til now I haven't found a solution.

I would be glad to hear from anyone who has any ideas or has solved this problem themseleves.

 

[MichaelRed]

"Tweak" is possibly not the term of choice. Ms. A. is well known as a resource hog, particularly on network operations. Your current set up is the generally preferred arrangement for multiuser db apps regardless of the 'language' howver the Jet db engine does have the unfortunate characteristic of always transfering the entire source recordset to the "FE" for each operation. It DOES appear to have a good chcheing scheme, but the more users there are the more often one can expect the cache to become dirty, and therefore the recordset sources to be refreshed (resulting in re-tranmission of hte ENTIRE recordset).


The only way I know to (realistically) overcome this is to replace the Jet db engine with one of the "indistrial strength" db engines (SQL server; Oracle; ...). If you had a relatively small number of users, or just want to 'experiment' with a relatively small db, you could look iinto the MSDE engine ('mini' SQL Server) which is available with Ms. A. 2K and later). It (MSDE) is a complete SQL Server, but only has 5 licenses and does not include the rather nice tools which come with the full installation.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jsteph]

...correct me if I'm wrong, but it's been my assumption, which unscientific testing has born out, that a dynaset recordset (with a properly indexed table and either the table as the source or a WHERE over an index) will only transfer the keyset over the network, and only when each record is visited with it fetch that row only.

My unscientific test is that when I open a dynaset on a remote table with 2 million records, each record about 1k, it opens in less than a second and I can start reading right away. A snapshot may drag the entire recordset, or a dynaset with WHERE clause not on an index.
--jsteph

 

[MichaelRed]

Not AFAIK. Ms. A. will start displaying the recordset well before the completion of the transfer in most cases. Also, the recordzet may be cached from prior operation.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jsteph]

...perhaps we're talking about different things. When you say << will start displaying the recordset>> that sounds like the internal recordset ms uses to display tables and queries in datasheet view. Here it does do a background fetch, which you know completes when you see the record count at the bottom, and the scrollbars navigate the entire table, as opposed to the displayed page.

However, in code, I can (fresh after reboot, nothing in any cache) open a recordset on a large remote .mdb table that I know to be about 600 meg (the only object in the db is this table, and the .mdb size is a tad over 600 meg in size, freshly compacted, with a single index), then pause the code, and look at a network traffic byte counter, and see just the random misc. traffic--no 600 meg table being schlepped across.

Access peeks into the remote .mdb with an offset into the file to find the record it needs, this offset is part of the index. So even though it's a file server architecture, it doesn't need the entire table to fetch individual records--if it has the index.

It's been my experience that many people don't index properly or they don't code sql to work with their indexes properly. Even seemingly innocuous sql clauses, like an ORDER BY that isn't really needed, or out-of-order fields in a WHERE clause, can kill a fetch of remote .mdb data. Too often they're used to starting out with a local .mdb, and these inefficiencies aren't noticed--until they split the .mdb and it hits the wall. Things like the showplan.out, (undocumented registry setting) can expose alot of this and make Access still reasonable even over a network and even with huge tables.

In the end, though, a 'real' rdbms backend is the only way to go for a serious app.
-jsteph

 

[evalesthy]

Here is an overview that may help you.

http://www.granite.ab.ca/access/performancefaq.htm

 

[jsteph]

<<it doesn't need the entire table to fetch individual records--if it has the index.>>

...correction I should say it can fetch individual pages...

-jsteph

 

[Falcon101]

Thanks guys for the Info and the debate. It has given me a few ideas and certainly the link supplied by Evalesthy is of immense value. (Tip definitely make sure no code runs when you run decompile. It will chew up your modules and spit them out :-(, otherwise it was of great value )

Just along the same thread of speed, there are a couple of sites that might be worth a visit

http://www.fmsinc.com/tpapers/faster/

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q240434