?
Solved

Access network traffic

Posted on 2009-12-21
3
Medium Priority
?
265 Views
Last Modified: 2013-11-29
I have a MS-A db in use by an organisation where IT dept. have raised concerns about possible future (not current) network traffic. They're keen for me to re-write to use SQL server as backend. System already split FE (on user's PC) + BE on server. Approx 15 potential users maybe 3 active at any one time. BE MDB approx 30MB but could grow to 150MB over next couple of years. 50 tables most with <100 records; 2 or 3 with potentially 10000; and 2-3 with 1M+. All tables linked with RI PKs etc. Converting the tables seems fairly straigtforward (I haven't used any fancy Access features) and all field names are already SS compliant. Main change would be Autonumber (to int(identity) in SS I think). All code (c.85000 lines in total) would need to be reviewed and edited to change DAO to ADO.

The change to SS may or may not happen (= be imposed upon me).

Meanwhile I'm seeking to make the system as efficient as I can so my question is: if a query in the FE includes a table from the BE with 1M records are all 1M records pulled across the network every time, even if only 200 are in the result? If so is there any way I can re-wite to mitigate this, anything I definitely should / should not do in the SQL?

I'm well aware MS-A isn't client server but are there any other options I might explore while remaining in MS-A? I have 15+ years working with MS-A, couple of weeks looking at SS.

Looking forward to reading any ideas.
0
Comment
Question by:cinclus
  • 2
3 Comments
 
LVL 58
ID: 26095941
<<if a query in the FE includes a table from the BE with 1M records are all 1M records pulled across the network every time, even if only 200 are in the result? >>
Depends on the query and the indexes involved.  You can use JET SHOWPLAN to see the costing plan that is getting executed.
JET will uses indexes whenever possible.  A full table scan is a last resort.
<<I'm well aware MS-A isn't client server but are there any other options I might explore while remaining in MS-A? I have 15+ years working with MS-A, couple of weeks looking at SS.>>
  There are various JET engine settings that you can play with (page timeouts, etc), but unless you have a specific performance problem already or a unique situation, the default settings are pretty good and probably should be left alone.

  The JET ISAMSTATS calls may also be helpfull when tuning (gives you basic stats on number of disk reads, etc).
 Also performance wise, keep a persistent connection to the BE from the FE over the life of the app (i.e. open a database object at startup, and close at shutdown).  Depending on the type of app, how it's used, and the coding, this can be a big performance boost.
 Doing so avoids the repeated open/close of the BE and it's associated overhead.

JimD.
 
0
 

Author Comment

by:cinclus
ID: 26096702
Jim

Sounds like its rather a case of "try it and see" for tuning and query design.

By "...open a database object at startup..." to "...persistent connection to the BE ..." I presume a table? Any significance to table size - could it be a single record table just for the purpose of maintaining the connection?

Thanks for the initial rapid response.

Nigel
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 26096915
<<Sounds like its rather a case of "try it and see" for tuning and query design.>>
Well that's pretty much true of any database; you need to know the usage patterns, how users might be searching, etc to get the indexes right. As for suggestions, you might also want to try running the database performance analyzer. It will give you some suggestions on indexing.
But the answer to your question more directly was no, JET doesn't always pull a full table across. It does use indexes to minimize the number of records it pulls. As your already aware, it does all the processing client side, so it pulls more then a true client/server setup would, but then the app is typically done differently as well.
<<By "...open a database object at startup..." to "...persistent connection to the BE ..." I presume a table? Any significance to table size - could it be a single record table just for the purpose of maintaining the connection?>>
Yes any size table or just the database itself. Here's what I have in my startup form:

Option Explicit
Dim rstBE1 As DAO.Recordset

Private Sub Form_Close()
10 On Error Resume Next

20 rstBE1.Close
30 Set rstBE1 = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)
' Open backend table in JET based MDB/MDE for performance.
Set rstBE1 = CurDB().OpenRecordset("tblSystemLogTypes")

End Sub
Which is opening a table. Doesn't matter what it is, as long as you force the database open. As said your mileage may vary. In some cases, this will have a tremendous impact and in others nothing as the app may already be acheiving this just by the way it's used or was developed.
CurDB() BTW is a procedure to supply CurrentDB() (see this article: http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html)
and in my linking code (which carried out before the app really starts up), I do this:
' Open the backend database
' This is so the backend is not repeatedly opened and closed
' Makes a big difference under NT
80 Set wrk = DBEngine.Workspaces(0)
90 Set dbRemote = wrk.OpenDatabase(strFileName, False, False)

which is just opening the database itself.
JimD.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question