Major Performance Issues

Hi there,

I am having huge performance issues with a FE/BE split. As a background, I have the following Table layout.



In basic terms, the contract table has basic contract info, resources can be assigned to a contract (via the Assign table) and we feed in exchange rate info also from another table.

Everything is so slow as soon as I put the BE on a share drive. I have done everything I can think of, I have changed to tlookups, I have changed the Auto options as recommended, analyse shows no issues, compacted the DB. It is unusable!

I notice on one form that it takes 1 second to calculate a field. The field basically uses a tSum to find the total cost of a contract (looks up assign to sum up all the attached resources). This seems to slow it down, but it does not explain the huge time it takes to load up.

I am considering that the issue may be down to the share where it is being located has too high latency (it is in another country, and it feels slow browsing through it)

Any other general ideas, or do you need more info?

Thank you.

salamander said:

it is in another country...

Click to expand...

Are you trying to use this over a WAN? WANs are no good for Access.

Check this out for a good explanation as to why:

http://members.shaw.ca/AlbertKallal/Wan/Wans.html

Yup, it's a WAN

Might have to consider using a MSSQL server.

Is it relatively easy to port the database over and use my existing front end?

salamander said:

Yup, it's a WAN

Might have to consider using a MSSQL server.

Is it relatively easy to port the database over and use my existing front end?

Click to expand...

Still not sure about a WAN. Perhaps this can help:

http://www.sql-server-performance.com/tips/access_sql_p1.aspx

just upsizing to mssql won't necessarily solve problems with a WAN

try to reduce the size of retrived datasets as much as possible - you can still achieve a lot over a wan if you do this.

also look at MS Knowledge base article 889588 - loads of recommendations to improve server performance

http://support/microsoft.com/kb/889588

finally, if you can use a Terminal server (by remote desktop), you can generally get very good performance.

It should be relatively painless to upsize the data to SQL Server and still use Access as the front end (and you can stay with an MDB rather than an ADP). I have a couple of applications that run very nicely over a WAN with that configuration. I will say that with either as a back end, you want to minimize the data that goes over the wire. You might improve your existing application's performance by making changes with that in mind (no forms bound to big tables, etc).

Thanks guys for the hints. Unfortunately, the scope of th e project does not allow for SQL Server etc. I have to be able to use existing infrastructure (i.e. a WAN Share)

I got it running fast though. What I done was use the replacement "t" lookup functions, calculated fields which depended on queries to not show immediately and optimise some of the queries (eg more strict conditions and avoiding using functions within the queries as much as possible).

This seems to have made it fine and more than acceptable to use. The only slowness comes in initial startup (up to 20 seconds) but since the app is opened morning and closed in evening that is fine. Reports also take up to 30 seconds but since their Excel reporting now takes >20minutes with incorrect estimations this is still a vast improvement.