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.

 

[behedwin]

No it is not building up, it was building up over time... from the beginning it was very fast.
Then after 3 months a bit slower... 6 months more slow... then suddenly i noticed it and it was very slow.
How can i tell if it is encrypted?! I have not knowingly done anything about that?

Never "re-linked" my backend and frontend since i started. All i do is create backups of FE and BE and put them in an folder.
Then i distribute a few user files of the FE.

Some things works fast, like small tasks.
But for example part of the database is storing staff information. So each staff have a profile containing data about that person, contract data, vacation data, what IT equipment that person have etc. Each user profile have a "log" where we store notes about that persons work, contract etc. So opening one persons profile takes the longest time.
Same principle is use for IT equipment for example. A computer have a profile with data about that computer, a log about what have been done to it, repairs etc. Takes a long time opening that profile too.

here is the screenshot: http://prntscr.com/n2u5kh

What do you mean queries return all field?

Dont know if my tables have to many indexes...? Looking in design view of a table in the backend all my fields are set to NO on the index option. Screenshot: http://prntscr.com/n2u70p

 

[behedwin]

Tried to copy my backend to a newly created backend file.
It did no difference in performance.

This is the code i use for one double-click action to open a form containing data about a staff member. Opens by doubleclicking a row in a lsitbox.
It takes 10+ seconds to open unless i put the whole database on my local computer and it opens instantly

Private Sub ListPicker_DblClick(Cancel As Integer)
Application.Echo False

If Form_SelectProfile_Form.Dirty Then Form_SelectProfile_Form.Dirty = False
If Not IsNull(Form_SelectProfile_Form.txtProfile_ID) Then
If ListPicker.ListIndex = -1 Then
Exit Sub
Else
With ListPicker
    DoCmd.OpenForm "Profile_Form", , , "Profile_ID = " & .ItemData(.ListIndex)
End With
End If
End If

If Me.Dirty Then Me.Dirty = True

Application.Echo True
End Sub

 

[xenou]

Not sure what will help. Just curious. With four users at once and some of them making edits, it's possible that users are locking records. You probably can use sqlite as a backend but I've never seen examples of that with MSAccess front ends so probably not an immediate solution but one that would require some research and changes to the frontend(s).

Do you have the same issues whether or not other users are logged in? Can you test with exclusive access to the DB? Sounds like you are saying its always a problem so that points to concurrency not being an issue, anyway. But if the problem is intermittent (like if you are the first one to log in and it's not a problem or everyone else logs out and you have faster results, then there is possibly a concurrency problem). Also if you are the only user and you log out, then the lock file should also disappear (since no one is logged in at that point).

Okay nevermind - looks like you just did that (last post).

 

[xenou]

You are 100% sure this is a local area network and not a file share on a remote network (something that would require an http connection across networks boundaries).

What's the code with Me.Dirty all about. Is this form always opening dirty???? That would be bad if users are using this form a lot and its going into edits by default.

 

[xenou]

If none of your fields are indexed then that's VERY BAD. What about your primary keys and foreign keys - are at least those fields indexed?

 

[behedwin]

all primary keys are indexed by default.
But no secondary keys are indexed... this is a thing i have never known about, so i have never touched that option in Access.

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.


edit:
did a copy of frontend and backend and linked them.
Then i set a bunch of fields to indexed (duplicates OK).
(not sure this is a valid test)
No performance change.

 

[bobsan42]

I don't understand why a form used to select some info to display will need to save back changes but it doesn't really matter - avoid saving data if not really necessary.
Is Form_SelectProfile_Form the same as Me?
And what is the point of this line: If Me.Dirty Then Me.Dirty = True

Can you post the row sources of the forms involved: SelectProfile_Form and Profile_Form
And if these are queries, can you break them all down in SQL with all preceding queries.
Also - is there any specific code triggered by _On Load, _OnOpen or _OnCurrent events of Profile_Form?

Good night everyone. Sweet dreams.

 

[behedwin]

Profile_Form have this on On Load

Private Sub Form_Load()
Application.Echo False

    If Not IsNull(Me.OpenArgs) Then
        Me.Profile_ID = Me.OpenArgs
    End If
    
DisableEditLogButton
DisableRemoveLogButton
DisableUnMarkLogButton
DisableMarkLogButton

GetBirthdayDate
GetBirthdayAge

If CurrentProject.AllForms("LonAll_Form").IsLoaded = True Then
     ' Form is open
     Forms!Profile_Form!Sida171.SetFocus
Else
     ' Form is closed
End If


Application.Echo True
End Sub

Profile_Form source: ProfileAll_Query
SQL of that query:

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.ProfileArchived) Is Null));

SelectProfile_Form source is Profile_Table



I am well aware that my code is not perfect. This is my first database ever and im very new to VBA.
So it have been allot of googling and testing.
But since it worked very fast in the beginning, no issues. And now it is slow. Even now when i go back to a old backup of the first versions it is also slow.

Been testing indexing the whole database and just some values... no changes in performance.

I cant see how my code that have been working for so long now suddenly should make this db very slow.

 

[bobsan42]

You are loading the complete profile table (minus archived ones) upon opening this form and then you only need one
I don't know how many records you have but you are pulling some 40 fields for each record.
A trick to speed up this particular form is to load only one record: Make the form unbound, then on load set its rowsource only to the record you need (include where condition for Profile_ID=xxx).
Or set the rowsource (bound the form) to a saved query for which you can modify SQL Text before opening the form - the idea is to extract a single record instead of all at once.
A general rule to speed up your database is to load only the information you need when you need it rather than all of it at once.

You may also try to time the other procedures included in the form_load event - see if any of them takes too long.

Do you show all the profile information on one page of your form or is it multipage? If yes - You can also load the data in parts - just before the relevant fields become visible.
Also ProfileArchived field should be indexed. Also try to play with the sort order of the index. I assume ProfileID is key field so it is indexed.
It is not a good idea to create too many indexes since maintaining them with each change will start to consume more and more time, but there is a necessary minimum.

I frequently refer to this: http://www.allenbrowne.com/tips.html

BTW, it is normal to expect slowing down as data builds up - that's when optimizing your queries and data access methods makes a difference.
A lot of things normally happen much faster on local drives so do not bother with this.

And moving you back end to a new location requires the links to the tables to be changed - in my words re-linked. So if you have used Linked table manager then you have done re-linking

 

[xenou]

Okay. Keep indexes on your back burner for future investigation. As noted above over-indexing is not useful. As a rule all the frequently used fields that are part of where clauses are candidates for indexing (for instance, a table of orders might have a secondary index on order date if you have a lot of queries that report orders by date). That should always help. Along with everything else mentioned above. None of which appears to actually help. In general the above post is also another tip that's also should help - if you need one record don't pull 1000.