Experiences running from network drive

General
1

Currently I am test driving an app that should be run from one of the network drives of our university. As the program is extremely slow to boot and sometimes has delays in accessing the underlying sqlite databases, I was wondering if anyone has experience with running apps from networked drives opposed to from the local computer.

Things that worry me:

  • our network drive is supposed to be quite fast, but it takes a lot of time to access, especially on my laptop
  • what happens if multiple people start the program? It has sqlite multi-user enabled, but will it slow down even more?
  • maybe the slowness is also due to using Windows 7. What can I do to improve performance on this standardised Windows version (my Mac is much faster)?

Of course any tips on improving performance are welcome. We chose to run from the network drive for several reasons:

  • we need to get access to the data during and after student employees worked with it
  • the app is in development and needs constant updates for added functionality (sometimes changing the database setup as well)
  • we do work with (limited) personal data and don’t want to have it on local computers (of course we do have our own consultants to make sure we comply with any regulations regarding this data)
2

I am afraid a slowdown could become one of your smaller worries. SQLite’s multiuser capabilities are rather limited, and on a network drive SQLite databases become easily corrupted. Doesn’t have to, but I would not risk it: Appropriate Uses For SQLite
I would rather employ another engine if ever possible.
And yes, it will slow things down even more.

3

Try replacing the database with a Mysql or Postgre one. Sqlite is known to be very slow on network-access and as far as i know it’s not reliable in a networkconstruction.

4

Do not use sqlite on a network drive. Multi-user is a bit misleading. It means multiple processes on the same computer accessing the database file. Once you get multiple processes on different computers, you’re asking for trouble.

5

I have had many sqlite files corrupted by running them off NAS shares (shared network drives). Running the binaries off the NAS will make it load slower (bigger latency than loading off local drive). if you need to run off the NAS, have the sqlite on the local drive (but that means it isn’t shared data). If you need to share the data, use a database server (some have mentioned Postgres and MySQL).

–sb

6

you can also use the sqlite file on the nas, but make a web app on the nas (if it is possible with your model) that access the local sqlite file, and distributes the datas to the remote web clients (or xojo desktops clients)
but yes it would be more robust to use a mysql or (prefered because really free) postgres database engine
they are both available on many nas models.

7

Rutger

You could take a look at cubeSQL

I use it for one of my apps built for technicians at my work and it has worked very well

8

Thanks for the replies. Unfortunately we do not have the luxury to just run a server due to security restrictions and the personal data issue.

Is there any other way for preventing database corruption? Maybe Valentina could be an option, but I am not sure if that solution is stable enough.

I must stress that the database is supposed to have only one user at a time. The drive itself is back-upped, be it only nightly.

9

Another thing, will a different sqlite driver (such as the Monkeybread plugin) make a difference?

10

Well, it is in the xojo documentation and also in SQLITE documentation.

For using MultiUser in SQLite: The processes using the database must be on the same host computer

So, NO SQLITE with more than ONE connection

The slow start is because if your app is 50Mb with the libs, those 50Mb should be transfered from the network drive to the pc before the app is started and EVERY time you launch the app, speed depends on newtwork infraestructure, network adapter, network traffic, etc.

The only option to use SQLite are:

  • A web app
  • A Client/Server app where only one instance of the server acces the database file.

NO

11

It is not a driver issue, Please do consider the recommendation to use a “real RDBMS” seriously. You are not using SQLite for its intended purpose. I second the PostgreSQL recommendation. Once the RDBMS is setup on a server of choice, the application can be launched locally. Only the data travels on the network. Load times will be drastically better. performance will be light years ahead also. “real RDBMS” are designed to handle multiple connexions.

Just a note. If your environment includes Win7 and Win 10 machines, make sure to check your SMB versions and update the Win 7 to the most recent specs. You may also have to configure win 10 computers to work with lower SMB revisions. This was a major issue with Windows 10 1803.

13

Pedro’s comment still applies and makes complete sense. if the app is physically located on the network share, then the whole app and libraries need to be loaded before the program will start. Every time. The performance of the NAS is likely not the limiting factor here, more likely the network communication speed. Especially when there is a lot of traffic going through from various points.

In a network environment, a real RDBMS is not an option, it is a necessity.

14

You do not know the can of worms that introduces… we have personal data, ports are blocked, protocols are extremely strict. It will take months to get permission if we ever get it… that is why other solutions should be looked at.

Possibly something like Berkeley db? (anyone implemented it for xojo yet?) Or returning to flat db if it really is the limiting factor.

15

[quote=410104:@Rutger de Jong]
Is there any other way for preventing database corruption? Maybe Valentina could be an option, but I am not sure if that solution is stable enough…[/quote]

It does not matter which option you go with, any approach based on a file share will fail eventually, it is the nature of the beast.

I probably should not do this but… if you are on Windows machines you could try using an MS Access database via an OleDb connection - using the MS drivers not some third party. It would be a step up from SQLite in that MS Access is sort of aware of network drives and multi user access where as SQLite is clueless. It will reduce the possibility of corruption but will not make go away.

16

[quote=410111:@Rutger de Jong]You do not know the can of worms that introduces… we have personal data, ports are blocked, protocols are extremely strict. It will take months to get permission if we ever get it… that is why other solutions should be looked at.

[/quote]
I’m pretty sure port 80 is not blocked… :wink:
so my previous recommendation of a local sqlite file and a web app is completely to consider…

17

[quote=410115:@Jean-Yves Pochez]I’m pretty sure port 80 is not blocked… :wink:
so my previous recommendation of a local sqlite file and a web app is completely to consider…[/quote]

But personal data is a problem…

18

Do you know how the xojo web apps work?

19

encrypt datas on the network. encrypt datas on the sqlite file.

20

It’s just as much of a problem with sqlite as with any other solution. What is the issue here?

21

WIth a shared database file, anyone can get a copy of it, or delete it. In a Web app or in a server/client app, the database file itself is not shared to everyone.