Sudden performance drop linking tables 1

[paradiso]

In my app I need to dynamically link tables depending upon initial user selection from a set of options. We've experienced a sudden, dramatic drop in performance in the link process. While following the code in debug mode today I waited 45 seconds just for this command to complete:

If DBCat.Tables("TblAccounts").Properties("Jet OLEDB:Link Datasource") <> LinkTablesDBPath Then
...

I have read the suggestions on this forum to forget about linking tables and to use the ADO connection string. That would be a very large job reprogramming so I am seeking help in the meantime. Has anyone any idea why the linking process should degrade precipitously.

I have decompiled/recompiled, then re-imported the whole thing into a new database. The linked tables are on a network drive, but my network people tell me there's nothing out of the ordinary.

Suggestions are welcome, and I thank you in advance.

 

[willir]

paradiso

From experience, there are two reasons that impact performance...
- resource issue - bottle-neck on the network, high CPU, high IO, insufficient memory
- time-out - can't find source, source locked

Isolate the problem by "divide and conquer".

Hmmmm, tough issue here. One noramlly does not install desktop applications on a server, so running Access on the same server with the backend (to rule out LAN issues) may be awkward. Perhaps you connect to a switch that also services the server.

What are your "ping" times to the server? Is there a relation to slow ping times and performance.

Copy the databse(s) to a another server and see if access is slow here too. (This will also test record locking issues).

Has the server been upgraded / patched recently?

Desktop issue. Is the network card, the NIC now running at 10 MB half duplex instead of 100 MB FD?

Is performance slow on many / all desktops or just one or a few?

What are the resources and performance like on the server when you try to connect.

Try connecting during periods when there is minimal network activity (very early, very late, NOT during backups). This may support a time out / lock problem.

There is more, but I think I have given you enough to start...

Richard

 

[4946]

If the tables you are linking to are large or are growing rapidly, (or something "unknown" has caused changes in the indexes) sometimes it helps to re-do (or create) the tables' indexes.

WinN.

 

[paradiso]

Many thanks to Richard and WinN.

To clarify the architecure, my front end db resides on the local machines of my two main users. The back ends (tables only) are on the server, so that users may write to a single set of tables. This is finance data. Each back-end database contains the tables pertinent to a single cost center. There are 12 of them. The staffer links to one cost center (i.e. tables database on the server) at a time, though it's feasible that they may both be linked to the same set of tables at once.

The slowdown is apparently network-related. Things move much more quickly early in the morning before many people arrive at work, during the day performance is slow from all the local workstations, and it is slow when I test linking operations on another server to which I have access. I suspect the network, not the servers or the local PCs.

I am gleaning information bit by bit. I wrote a quick benchmark program to time the creation and writing of a 1 MB file onto a target and found that writing the file to the server takes 5 times longer than creating and writing the same file onto the local C: drive. I just learned yesterday that the server is running Norton Antivirus on every file I/O operation. That could be a slowdown for my benchmark program, but would it affect 1) a linking operation and/or 2) an SQL retrieval.

Bit by bit I'm working through the suggestions you sent, and those offered by others. It's slow going.

I thank you for your attention.

p

 

[willir]

paradiso

Based on the information you have supplied, I think I know your problem.

Access is a light weight database applicaiton in some respects because of it's limitations for networking. I guess the expectation is for companies to upgrade to MS*SQL or some other database engine solution if they have sufficient cause. The inertia to the upgrade of course is money to purchase the heavier hitter application, and the cost of supporting the application -- it is so much easier and less exepensive to use Access.

When you create a form, it is usually tied to a table or a query. You want to look at customer table, order table or invoice table type of thing. When you load a form, Access copies the entire data set tied to the form across the network - the entire customer table, the entrie order table type of thing. This copy action occurs even though you are using a backend / frontend solution.

With a small number of users and/or a small databaes, this copying of records will not significantly impact the network. However, with more users accessing the system, larger databases, and perhaps frequent openning and closing of forms may result in excessive traffic.

The high end databases work a little differently in that as I understand it, only the current record or block is copied across the network. They are kinder to the network in this fashion. (They handle records locking a lot better too.)

Some things you are problably already doing...
- You do have a backup procedure in place (of course you do)
- Run the compact and repair

Here are some options to consider...
- Archive older data to make the size of the transaction tables smaller.
- Redesign commonly used forms so that they only bring acorss minimal information. For example, the primary key and maybe one or two search fields. Then use the primary key to retrieve the current record and display it to the form. this way, the information is copied one record at a time. For example, for an order entry system, base the form on the customer code and order number with unbound fields and/or subforms set aside for the other inforation - address, shipping info, product orderd, etc. For the current record event procedure, use customer code to retrieve the customer information, and the order number to retreive the current order information. The code then displays the appropriate information in the alloted field or subform. A lot more work, but I have been told this really improve performance.
- Use a Terminal Server solution. Put the front end and back end on the terminal server. Users connect to the terminal server to access the Access database. Now network traffic is mostly restricted to the terminal server. You will have to make sure this solution accomodates printing.
- Should you upgrade to MS*SQL or Oracle?

Richard

 

[paradiso]

Hi Richard,

Your reply was heartening and disheartening at the same time. It was heartening because it reassured me that I'm doing a lot already to improve performance. All my forms are unbound. Selection screens send an id and the rowsource of the form is established via dynamically-formed query on the Load event. Many combo boxes on the forms are similarly unbound, and populate themselves depending upon other entries on the form. However, there still might be hidden network burdens in list boxes and combo boxes that must retrieve a lot of info. I will continue to examine these.

Further, we keep each fiscal year's information separate from other FYs, the user can relink to whichever cost center and year they wish. This problem is most annoying and evident now because it is happening just one month in to the new fiscal year. Not much data is being retrieved.

Now for the disheartening part. Do we need to upsize to SQL Server. Despite 10 years of Access database and Access VBA under my belt I'm concerned that it would cost a bundle for my client to pay me to learn a new system and convert my code. However, I will do research on it and see if the fears are founded.

I knew this day would come. I must seize it.

p

 

[willir]

In Dead Poet's Society, John Keating (Robin Williams) said to his class

Carpe, carpe diem, seize the day boys, make your lives extraordinary

Not to solicit a track to follow, but various DBA's have told me that MS*SQL is pretty nice - support tools, and it has an upsizing tool.

Do a search on some of these forums for the task -- there are a few pitfalls, but perhaps much better than re-doing everything.

Richard

 

[paradiso]

Have just finished reading the chapter on upsizing in F. Scott Barker's Access 2002 Power Programming. Thank goodness many of my programming and design practices will facilitate a conversion, but there is still good honest work to be done.

Many thanks for your generous suggestions and support.

carpe diem,
p

 

[willir]

Read the following excellent post by BSman...

http://www.tek-tips.com/viewthread.cfm?SQID=753848&SPID=700&page=1

Give him a star if it helps...
Richard

 

[paradiso]

A star for willir (Richard) for his excellent responses to this thread.

paradiso

 

[willir]

Thanks you. And good luck with the next phase...