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]

Is there a non-IP filepath you can use? Is this a LAN?

 

[stumac]

Another point I can't see if anyone has mentioned previously is that running the front End from the network drive can often be really slow. typically each user will run a copy of the FE from their local machine with just the BE saved on the network.

I have tested this with a 4mb front end on my computer a fairly complex query takes 6 seconds to run v the network (where I keep a master copy) it takes 26 seconds to run the same query. both link to the same networked BE.

 

[bobsan42]

You do not need to provide anything. Just to make sure you backend is connected (and it must be for the application to work).
The function ListBESources will find the backend file(s) and return a list to the "mother ship".

If you look in the comment lines you will find this:

'Invoking the Procedure
'Call this when your application starts:
'---------------------------------------------------------------------------
' OpenAllDatabases True
'
'When you finish, call this to close the database variables/handles:
'
' OpenAllDatabases False
'---------------------------------------------------------------------------
'For instance, if you have a form that controls the application and remains open during the entire time the user is using your database, add the code to the OnOpen and OnClose events.

Basically you have to find a suitable way to open you back-end upon starting of the application (yes, some start-up code, or do it manually through the immediate window to check if it helps at all). To do so use this:

OpenAllDatabases True

And make sure you close it when shutting down:

[COLOR=#333333]OpenAllDatabases False[/COLOR]

You can check the back-end-opened status in at any time (e.g. in the immediate window of VBE) like this:

?[COLOR=#333333]OpenAllDatabases , True[/COLOR]

 

[behedwin]

I tested this opening all databases code now.
i get no performance boost atall from using it.


what really blows my mind is that I took my computer, connected it to my mobile network and then connected to my works VPN and then went to the database file on our network.
There was an significant difference in speed, the file was not as fast when i take it offline but it was deffently faster. Tested back and forth several times.

I have tested placing the frontend file local on my C: drive
and i have tested having the frontend on the network folder close to the backend.
No performance changes at all.


I cant for my life see how my database is slow because of bad code or querys when it is fast offline from the network and even a bit fast when connecting to it outside of our network with VPN.
It is just strange.

 

[behedwin]

Another update on this.
I did a test. Created a new access file, connected it to my backend. Copied over just a few querys and forms.
Still this new very thin file was very slow aswell.
But using the new file over VPN and it was fast. So still only slow when at work, working to our network folder.

I think my total size of database is quite small and should not be slow.
It have :
9 modules
11 reports
23 forms
33 querys
15 tables
Total size of backend: 1,7mb
Total size of frontend: 6,8mb
And yes i have done repair thingi in Access


All i can think about now is that our internal connection from our offices are slow to this network folder and when i connect to VPN or when our IT department test the speed it is not telling the truth. Could this be a realistic scenario?

 

[xenou]

Well first of all it should always be fastest when connected to your own local hard drive. So no surprise there. VPNs are normally pretty fast too. So also no surprise there. As already mentioned above, there is no general cure for this kind of problem. And you have tested the speeds yourself so there seems to be no dishonesty on the part of the IT folks.

Another oft-cited performance boost is not to nest folders deeply for access databases:
\\192.168.11.21\Gem_Exp\ent\Personal\Lista\Filer\BACKEND\
so better would be:
\\192.168.11.21\BACKEND\

Note that by "oft-cited" I mean something like "people say this helps but I have never seen any first hand proof of it"

I still find it curious that you are needing an IP address for a network drive. Is there no mapping of drives or share names at your work? How many people are using this database? Have you ever considered using a server-based engine for your backend? Also why is your front end so big compared to your backend? Usually it should b be the other way around (not only is your database small, it's tiny). This really feels like deja vu somehow, actually.

 

[behedwin]

I think my ffrontend is a bit big because of 5 reports containing pictures.
Other than that i have no clue.
If my db is considered tiny, still it can take 10 seconds or more to open a record on a form when on network compared to instantly on local drive.

Never considered anything other than access db since i dont know anything other. What is a server based engine for backend?

We are atm 8 ppl using the db.
The largest table got about 1500 rows.

Tbh i dont know if i can reach the network folder with some other name than the IP.
I will try and put the db at the root.

Thing is that this issue was not from the beginning. It came gradually and started to be an issue for about six months ago.

Asked the IT folks for a new location, preferably on a new server to see if i get any changes in performance.

 

[xenou]

with 8 people using the database are the often using it at the same time or just going in and out of it during the day (but generally, only 1 person actually interacting with the database at one time). Or are they all really using it simultaneously?

Are they all reading and writing to the database (select, update, insert, delete) or just reading (select).

Where is the data coming from? Is it static data (not changing) or dynamic data (always changing, new data, updated data?)

As for definition of a server database engine - that's pretty much any other database engine. The only other serious file-based database out there that I know if is sqlite.

 

[behedwin]

Id say out of 8 user. 4 use the db daily and normally no more than 2-3 ar in at the same time.

All users can edit and read. But mostly it is 4 that do it.

New data is added daily by atleast a few ppl.

Is it worth/possible to export my backend to SQLlite and keep my access frontend. Is it possible, will it be better for performance? Is it much work to make it work?

 

[bobsan42]

Is the slowness building up? Is your backend encrypted?
Does it take very long to re-link your tables when backend is on the shared folder?
Does everything work very slow or some things work faster? Do forms open slow in design view? Do you use runtime library or full version?
Can you post a screen-shot of the Advanced section in Access Options/Client Settings? (mainly look at Record locking and Encryption method)
Do your queries return All fields? Do your tables have too many indexes?

A thing you can try is to copy all your tables in a newly created accdb and use it as backend - see if there is a performance change.

I can think of too many things that may cause delay ...

If we take away network bottlenecks (I personally wouldn't discard it lightly) then it remains to be a database issue. Admittedly the database backend is too small to have performance issues.
And this has to be thoroughly investigated if it is such a problem. Focus on an operation that takes too long, break it into distinguishable sections and time each one look for discrepancies and try to find the reason behind.