[RESOLVED] Performace Issue

[Katuil]

This is a strange one. I will need to explain the network configuration before explaining the issue.

Network Config:

My companies network has three T1's, and several satelite offices. In our main building there's no issue. The issue is at the satelite offices. The T1's are configured in the following manner: Internet, Data, and the third is shared between the field offices each getting a 512 pipeline. We have a Cisco 2620 Router configured for 512 pipe.

Issue:

We have several MS Access databases that the field offices access and they are getting very poor communication. Everything else (Internet, browsing the network, etc..) is lightning fast. Once you access the data, speed improves, but th initial connection to data is vey slow. Inthe main office a form that opens in say 5 seconds can take over 5 minutes to open in the field office.

Database Config:

The MS Access databases are 2000 and configured client server or 2 tier in design. They are mdb and not adp. There's a mdb that is local to the workstation and a data side that resides on one of our servers. I am going to try adp with MS Access as the data side to see if this will help. These databases are going to be migrated to MS SQL Server but that will not happen for six months or longer. The other solution that I am going to try is to use VB 6 as the GUI to connect to the MS Access database. Both these databases are already over MS Access level for data, they are 40 Meg and 150 Meg in sizes. Both can have three or more users at any given time. Both run without any performance issues in our main building where the servers reside.

If any of you need more information let me know and I will get it posted. First time that I have had this particular issue. I did run a test with VB on a 256K pipe and it ran great. But that was using the VB standard data object. Using a data environment (designer) it had the same performance as the MS Access database does.

Appreciate any help or insight that is offered.

[Lord_Rat]

A satellite has a long ping time, therefore, if small amounts of data are sent in succession, it takes a long time to send that data.

If large quantities of data are sent at one time (like a Web page) then that data can be sent quickly because it is all one package.

Access does a large number of queries to itself anytime you move around in Access. These queries are small and on a system running over a LAN or LAN-equivelent, this is normally not niticeable. In the case of satellites, however, the ping time greatly slows this down as each request has to complete before the next is sent.

If you want to see the difference in ping times, ping a computer on your LAN and then ping a computer at the remote location.

You will probably find that there is more than a 20 times greater ping time.

As long as they run Satellite, you are going to have this problem.

Sorry.

[Beacon]

I agree with Lord_Rat but for a similar reason.

When open a connection or recordset to a Access db over a network. It copies the entire db over the network.
This is another reason why it's so slow.

Also when you say CLient Server configured how do you mean??
Access is not a client server database. You can imitate it to be client-server though by making a winsock app that interprets sql commands.

If you want this fixed i would go get SQL Server.

b

[Katuil]

Beacon:

If you would have fully read my post then you see that the solution that we are going to is SQL Server! But that will not happen for several months. Along with Client/Server, if you would have fully read then you would understand as well. If you know what Two Tier application is then you should kow what I mean. I do not mean to be a jerk about your reply but please read completely and if you do not understand then ask.

Two Tier applications are applications that have two pieces to them. Normally there's a piece that is on the server, and then the local workstation (the users pc) has the other piece. This is as refered to as Client-Server application. So with MS Access what you would have is this: A data file that has only the table structure and data. The other piece is on the users pc and has the GUI. It also has linked tables from the data file on the server. Also, I never said that Access was client-server.

By the I have found a solution other than SQL Server.

[barchetta]

One possible solution to improve speed could be using replication. Store the replica's locally and update them once every given time. This doesn't work too well when entered data needs to be directly available to the other replica's though.

[crosbj]

Both these databases are already over MS Access level for data, they are 40 Meg and 150 Meg in sizes.

What does this mean? Are you still adding records?

[Beacon]

Originally posted by Katuil
Beacon:

If you would have fully read my post then you see that the solution that we are going to is SQL Server! But that will not happen for several months. Along with Client/Server, if you would have fully read then you would understand as well. If you know what Two Tier application is then you should kow what I mean. I do not mean to be a jerk about your reply but please read completely and if you do not understand then ask.

Two Tier applications are applications that have two pieces to them. Normally there's a piece that is on the server, and then the local workstation (the users pc) has the other piece. This is as refered to as Client-Server application. So with MS Access what you would have is this: A data file that has only the table structure and data. The other piece is on the users pc and has the GUI. It also has linked tables from the data file on the server. Also, I never said that Access was client-server.

By the I have found a solution other than SQL Server.

I know what a two-tier application is and your sarcastic pretentious reply is not appreciated nor needed.
I'm glad you solved your problem and hope you never visit these forums again.