improve performance over network?

[OTWarrior]

we use a frontend to backend system of access on our network, and the 4 teams have different permissions to parts of the frontend. Unfortunately, due to the mass of data going across the network, it can get quite slow (in fact, it is alot slower than it used to be)

basically i need advice on how to increase the network speed of the whole thing (i don't have server maintenance priviledges, but can do what i want to the backend).

I was thinking of spliting the frontend into 4 versions (one for each team), and also splitting the backend for 5 versions (1 for each team, and one for where the data would be replicated/crossed over)

if i were to do this, would it make it faster? both the frontend and backend files are 60mb each, so are at their limits (as far as i know).

OR...should I get the frontend to load all the forms that are commonly used when it loads, but makes them not visible, and either makes them visible or not visible depending on where they should be. This could be slow for the computer but could it be faster for the network?

any other ideas would be greatly appreciated.

[keaian]

I guess i need a little more information about then network it's currently running on. 2003 server? 10mb, 100, 1000mb, 1gb connection?

I don't think splitting the program into 4 different versions is going to make much of a difference because then you are going to have the exact same amount of data transfer happening on the network. 60mb db is pretty small. If you think it's the size of the database then run a daily compact and repair to see if the actual size of the db is slowing you down.

Lastely, consider on how all these computers are connected. Via hub or switch. That will make a world of difference if you have alot of users.

[OTWarrior]

i do a compact and repair daily anyway to help.

I do not know the size of the network bandwidth, though would assume it is 1gb as it is a very large network. And i do not have any access to the network side of things, only the database that we have developed.

I was mainly wondering of any tricks that other people have found to help. I tried turning it into an MDE file, but although generally faster, it seemed slower at other points.

[keaian]

not sure how it's setup on your network, if everyone just has a shortcut to the network database but what you can do is make a local copy on their computer with linked tables to the backend. also if you are running virus software, see if you can add your database as an exception so it doesn't try and scan it everytime someone does something to it.

[OTWarrior]

i already mentioned we are using a frontend to backend set up.

the virus checker idea is rather good, although i doubt i have access to the settings (can't even open up the date/time settings on the computer or defrag it)

[XLGibbs]

A lot of things can impact network performance, specifically if there are frequent table locks and such with that many users.

If the front end is primarily used as read only, there may be option for optimization within the back end. Data normalization is critical, as well as good primary keys and relational integrity.

Having the forms open on the local machine and remain unseen "may" improve performance provided the forms don't read in large datasets or create any locking issues if the front ends are performing frequent updates.

[OTWarrior]

any ideas how i could get around table locks? there are 50 Users on this database, and so tbale locking could be a major factor.

also, i did read that setting Subdatasheet Name property to [NONE] on each table can help performance, but would it damage the relationships of the tables? or is it a redundant option? (it seems to be set to AUTO on alot of the tables)

[XLGibbs]

I would presume the locking is a major a factor then, but there are possibly some other ways. I have lived with a SQL Server for almost 3 years, so let me do some digging on Access and get back to you.

And by the way, you must not read the internet much,
NOTHING can save you from a chuck norris roundhouse kick,
not even VBA tags--as powerful and important as they are.

[Qubit]

Just a thought...I have always used a mapped drive to point to the network share:

R:\ = \\server\datashare (file://\\server\datashare)

All the tables are linked to the MDB in R:\. If you have to re-locate the backend MDB, you then re-map (R:) to the new share. Much simpler all round, and it does speed things up - I think because the 'share resolution' happens only once, when the drive letter is mapped, instead of each time a table is called for.

Cheers.
.Q

[OTWarrior]

we run a VB script that maps the drive on bootup (Sorry, I forgot to mention it) thanks for the suggestion though Qubit.

[OTWarrior]

here is a screenshot of the advanced properties of the database. Is there anything I could change here to make it quicker?

[XLGibbs]

Not necessarily. There may be more under-the-hood changes necessary. I am still digging around for some answers on my end for you.

[XLGibbs]

I would look at the table structure, query design and possible improvements to the underlying structure. It is possible and not unlikely that some of the performance issue are found in the underlying structure and mechanics of the database itslef. (it's as true for a small database as it is for a big one).

If there are redundancies or poorly structured data..it may cause issues.

None of the settings in that screen will really improve performance by changing them.