Access Speed Question

[ckaspar]

I have inherited an Access Database that is being hosted on a SBS 2003 Server. All network operations are "smoking" excluding a user accessing the database.

The database is broken into two sections. The 'Server' section contains only tables.

The 'Client' Side contains linked tables to the 'Server' side, queries, forms, reports, macros and VBA code. The 'Client' is stored on each client computer of the domain.

When a user from a client computer of the domain opens and uses the database using the 'Client' portion it works fairly quickly. When a few users connect using their 'Client' program the database slows down for everybody.

I have gone through both the 'Server' and 'Client' and optimized the best I can without re-designing the whole thing.

What can I do to maintain the ease of GUI updates allowed by having the DB broken into Client/Server while speeding up the data access for the clients.

At any one time there can be up to 15 clients connected to the DB which slow it down considerably.

I have tried using the replica feature of Access and it helped out a lot but I onlt tested it. I am worried about rolling it out across the network with knowing all of the repercussions.

Any help provided would be appreciated.

Chris Kaspar
Independent Systems and Software

http://www.independentsystemsandsoftware.com

Twin Town Treatment Centers

http://www.twintowntreatmentcenters.com

 

[BobJacksonNCI]

Hello Chris,

When you went through, did you check that indexes exist, where appropriate?

Also, while I've read a number of posts that indicate Access databases slow down when the number of active users exceeds 10, I haven't read of speed problems with a "few" users. What constitutes "few" for you?

Have you looked to see that queries are passing the least amount of data required from the server to the front ends? (It doesn't take very many queries, each passing hundreds or thousands of records, to bring a network to its knees.)

There are eight active users in a database I wrote and I was just now able to pass 10,000 records quickly - because none of the other users are passing more than a handful.



HTH,
Bob

 

[Terpsfan]

The reason you are getting poor performance is because Access tends to degrade in performance when you get to 15 users across a network. Because Access uses a network to pass the data across to the client, it starts to bottleneck because the network traffic picks up.

Access tends to slow down in a multi-user environment also because of it's locking scheme with the LDB file. Make sure that you are using a Records Lock = No Locks in your form's properties. Some have also said that keeping a hidden form connected to the backend also helps performance.

A better way of fixing this problem is to either 1) migrate to MSDE or SQL Server as the backend or 2) retain Access as the backend and create an unbound forms architecture where you only keep the connection open to select, update or delete records. Both of these measures will unfortunately involve a rewrite to the application to some degree.

I'm assuming your network is at least 100 mbps as opposed to 10. An Access database can only run as fast as the network it resides. But you can do things to optimize.

 

[BNPMike]

I've worked on mainframe databases where the application slows to almost a dead stop with a few users. There are loads of reasons why this can happen. Don't jump to conclusions about Jet or network connections.

You need to look at all the contributing factors - complex SQL, lack of indexes, creating recordsets with unnecessary records (eg table instead of a query), excessive SQL-driven controls on forms, locking - index locks/driving users to the same pages of tables etc etc.

You'd be surpised what bodging has to go on with corporate applications on top-range servers.

My Most-Wanted vote would however go to SQL - can you think of a more efficient way of doing what you are doing? is your SQL more than 2 lines long? Are you using sub-selects and loads of calculated fields, particularly in WHERE conditions...

I've seen the most staggeringly inefficient SQL run on Jet in a fraction of a second. However eventually reality catches up with you, and that place is the multi-user app.

 

[ckaspar]

Thank you for all of your suggestions. I really appreciate it. If any of you know, why would the replication test that I did cause the DB to run so much quicker on the clients?

I plan on migrating to a web based interface in the future using Access with ASP and IIS but we don't have the time or development budget to build that right now. I am attempting to put a band-aid on the situation until we can migrate to the web based interface.

Any thoughts you could provide in regards to the above would help.

I have gone through the DB and attempted to identify what needs to be indexed and made those changes. I have also tried to reduce the # of records and the data contained in those records to try to speed it up, so I am on the right track. I just don't want to invest too much time in the re-development of something that will be obselete in a year.

Now that I think of it I have one other question which I hope you all can help resolve for my head. When the interface was originally designed the designer used a lot of embedded images and backgroud colors. How much of a role if any would that play on a client/server app that I explained above?

Any help would be appreciated!


Chris Kaspar
Independent Systems and Software

http://www.independentsystemsandsoftware.com

Twin Town Treatment Centers

http://www.twintowntreatmentcenters.com

 

[PHV]

lot of embedded images
If the images are in the local computer (FE), no prob.
If in the server ....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ckaspar]

Well, the embedded images are on the client so I guess all should be OK. Once their cached they should not lag at all.

Chris Kaspar
Independent Systems and Software

http://www.independentsystemsandsoftware.com

Twin Town Treatment Centers

http://www.twintowntreatmentcenters.com