Slow database - only on network folder

[behedwin]

I have a quite small database put on my companies network shared drives.
The database is split into two files. The connection is set to IPADRESS\FILEPATH.

I have huge problems that the database is very very slow when users works in it.
I have searched for a long time for an answer to this, but not been able to find any solution.

If i take the database offline, reconnect the two files localy on my computer it is lightning fast. But as soon as i put it back on the network drive it is slow again.

I have contacted out companies IT department and asked them. They say that they have tested the network and it is fast and no bottlenecks in transfeering files on the network. They dont really care about my issues so i cant get them to be very motivated about this.

Is there anything i can do?
Running a test on my computer i get 80mbit download and 8mbit upload speed on the internet.
Dont know how to test write/read speed on a network folder.

 

[xenou]

Found an backup of a frontend file that is over a year old.
Tested running that one connected to "todays" database (backend) file.
It was just as slow as todays version of the frontend.
This should exclude that i have added some code during the year that messes up the speed.

Might be interesting if you also found a copy of the backend file from about the same time a year ago...

 

[behedwin]

Ok i have done some things.


1. Profile_Form
Now the source is a query called Profile_Query instad of ProfileAll_Query
The SQL of that new query looks like this

SELECT Profile_Table.Profile_ID, Profile_Table.FirstName, Profile_Table.LastName, Profile_Table.Personnummer, Profile_Table.AnstalldSom, Profile_Table.JobbarPa, Profile_Table.WorkStartDate, Profile_Table.WorkEndDate, Profile_Table.JobbProcent, Profile_Table.AnstallningBelastar, Profile_Table.Mandag, Profile_Table.Tisdag, Profile_Table.Onsdag, Profile_Table.Torsdag, Profile_Table.Fredag, Profile_Table.Address1, Profile_Table.Address2, Profile_Table.ZipCode, Profile_Table.City, Profile_Table.Phone1, Profile_Table.Phone2, Profile_Table.Phone3, Profile_Table.Email1, Profile_Table.Email2, Profile_Table.Anstallningsform, Profile_Table.IKT_Ansvarig, Profile_Table.IT_Ansvarig, Profile_Table.Skyddsombud, Profile_Table.SBA_Ansvarig, Profile_Table.Forstelarare, Profile_Table.Arbetslagsledare, Profile_Table.Belastningsregistret, Profile_Table.Tystnadsplikt, Profile_Table.Aktuellt_Avtal, [firstname] & " " & [lastname] AS Fullname, Profile_Table.ProfileArchived, Profile_Table.ProfilePicture, Profile_Table.lon, Profile_Table.avtaltyp, Profile_Table.timme, Profile_Table.chef
FROM Profile_Table
WHERE (((Profile_Table.Profile_ID)=[Forms]![SelectProfile_Form]!
[ListPicker]) AND ((Profile_Table.ProfileArchived) Is Null));

This query only opens the selected Profile ID from the form SelectProfile_Form


2. @xenou

I did find a old backend and an old front end.
I re-linked them to each other and tested.
Need to test this tomorrow back at work to get the real deal... at home connected over VPN it works fine, but the problem is at work


3. @bobsan42
All profile information is on one page so i need to load one record/row atleast.


Would you guys recommend me setting all my primary keys (they already are) to indexed and also all my secondary keys to indexed "no duplicates"?
And for example when loading a profile with many fields on one page. Is it worth indexing more of those fields or is the ID enough in a situation like this?



None of my changes havent given any real effect since im at home now connected with VPN to the files.
But this worked before, so i need to test all above changes tomorrow morning.


I really hope i can see a performance change in the process from select profile in SelectProfile_Form, doubleclick the listbox row, open that selected record in Profile_Form. If i can cut down the loading time on that process 80% of all issues are solved.

 

[bobsan42]

Key fields are indexed - no other way.
It is not always possible to have no duplicates in indexes.
Are your primary keys simple (made of 1 field index) or complex (indexing several fields)? ( I don't really know if it makes a difference - just wondering)

 

[xenou]

Just to note that "secondary keys" is not something defined automatically by access. Even the definition of a secondary key might be up for grabs. The important thing is that you identify candidates for indexing and put the appropriate indexes in. Primary keys, however, are always indexed by Access (that was a trick question when I asked you if your primary keys were indexed ).

 

[xenou]

I really hope i can see a performance change in the process from select profile in SelectProfile_Form, doubleclick the listbox row, open that selected record in Profile_Form. If i can cut down the loading time on that process 80% of all issues are solved.

At this point I am hedging bets. Probably won't help but still worth a try. Let us know what happens.

 

[behedwin]

Sadly you were right. No changes in performance atall.
Still slow as a cow opening the Profile_Form from SelectProfile_Form


I am back on the network problem issues. Yes VPN and local is faster and that might be normal.
Sent a mail to IT and asked for a new folder temporary on another server as far away from my current location as possible..... just to test if there is any difference.


Is there any other options to host an access db to let a few users work in the same file... can the backend be placed somewhere else?

 

[bobsan42]

Sorry to hear that.
Is it a wired or wireless network? or a mixed one?

You can try various shared folders - not necessarily on a server but on any computer on your network and see if there is a change?

 

[behedwin]

It is both wierd and wireless. Tried to connect with both options.

I did a test now with my college. I shared a folder on my computer, put the backend on that folder and re-linked the frontend.

THIS WORKED!! It is lightning fast for both me and my college working on different computers than the backend is shared from.
So a simple solution is to setup an old computer, share a folder and let ppl connect to that one when working in the database.
Problem is that im leaving this workplace in a bit and cant maintain it.

 

[bobsan42]

So it is a bottleneck somewhere on the network. May be even server priorities.
Good luck with your new work(place). Don't bother about maintenance - someone will have to learn if they need it.

 

[welshgasman]

PMFJI, but in one place I worked, we had the server on the same desks as our group. However it was still painfully slow.?

Turned out that our network traffic was directed off to somewhere and then coming back to us, rather than coming straight to us. You would need to be a network guru to work this out. :D

Also make sure you keep a connection open to the backend at all times. I used a login form to do this in the last place I worked.

https://stackoverflow.com/questions...-persistent-connection-to-the-linked-database

HTH