Hi,
We use SQL Lite format for our data files.
I had a client say the app was locking up for 1-3 minutes at a time when they went from one screen to another.
Remoted in and saw it myself… my program would go “unresponsive”.
Turns out he was opening the file from a flash drive.
I saw journal files on the flash drive.
Moving file to hard drive or even OneDrive and opening it… problem goes away.
This was a Windows 10 machine.
Is there a way to code around this? Or if I’m using sql lite files just tell them “don’t open from flash drive”.
Tested on El Capitan (macOS) with a small 601 088 Bytes .sqlite in a 2MB Memory Stick (if the compare can mean something): some seconds only (2 or 3, too fast to realize the speed).
Seems the same time used to copy the file from the internal SSD.
Have you tried a small .sqlite file ?
How large is the used file.
Test done on a Windows 10 1709 laptop:
Doh ! I just tested the same application, with the same small file (601 088 Bytes, 6 user TABLEs, one with the list of data/Records) takes around 10 seconds.
The Doh above was for the surprise, because the time to load the data is not but the load on so few data is different.
The file size for our app is less than 30kb. So it’s not the size.
On this client’s computer the lock up would be 1 entire minute or longer.
I could copy to a temp file and back but
1). How do I detect they are opening from flash drive?
2). If I copy to a temp location and then when they close the file or exit the app…copy it back… WHAT if they pull the flash drive out before the app writes that? They are in app working on file… think it’s saved and just pull out the drive.
A HDD/SD/… failure can always happen and removing a drive while data is not entirely written to this space is only 1 worst case scenario.
If the data your App is working with is REALLY important, your app should always kepp Backups and work with temporary files.
And while you are working with temp. files anyway, why not put them on the fasted available space available to your app (maybe even in the computers ram?)?
I have a file close which is obvious to copy back but if they exit the app / quit…
then How do I make sure the copy back occurs before the quit?
I see SpecialFolder Temporary to use for this.
Are there any standards I need to use for putting files in Temporary?
Do I need to add my company name as a folder?
Or just copy the file there at the main folder path?
i.e. does it matter
1). Even if I coped a file from the flash drive to Temporary and user’s edits are stored there… what do I do when they pull out the flash drive? Later when they close or quit and app tries to write back it will fail. Then what do I do? Store the temporary file somewhere in backups and just alert the user?
2). I am using sql lite and Storm. How do I put the file in memory and point to that so it’s not writing out to the hard drive and to journal files etc.?
I’ve never done this before. Maybe by using a MemoryBlock? Because i am lazy, i think i would simply create a SQLite Database and use this as a storage, if possible.
There is something you are doing in that case that I do not do.
Another variable for the slow down can be the size of your clients data base file.
I understand the inconvenient, but maybe the client have to copy the file in a SSD (fast read/write mass storage) if the file is large enough to slow down the whole process (whole hardware / software. When done, (s)he have to copy it back into the MemoryStick once modified.
important to remember that SQLite creates Journal files when you open a database… with Xojo you have no control of where they go, but “usually” they go right next to the DB, which can cause problems with Sandboxed apps, and other situations
No… the journal files are not made to waste time, they are created to speed things up, allow for rollback etc.
and the journal is not much bigger than the “about to commit” data, which is usually quite a bit smaller than the database.
