[TKW]

Hi,
I'm using MobileSheets on Windows 11. Local sychronizing libraries takes for me about 1 hour which I consider quite unhandy and unreasonable long.
Out of this ~1 hour it takes about 50min until the 30MB library entries are exchanged between the two PCs and then a few minutes for the real mass data (PDF and MP3).

I observed the following behaviour which is a bit strange to me:
Until the database is completely transfered from Server to Client there is NO progress on the client side. This took in my tests up to 52min.
On the server side it looks only a little better as at least the first two lines from the protocol appear: IP & "Database will be sent to connected device". The progress bar will show 0% on the server until the first song compare happens (after 52min respectively 67min)
After that the the real data is exchanged and the process windows show action. PDF and MP3 transfers work with reasonable speed, considiering that for all files the directory entries are checked again.

I don't mind that the first sync takes a long time, but after only minor (some notes or adding one single song) or even no changes, it takes the same long time for the database exchange. Even a FULL copy (with all MP3 files) on my network could be done in less then 10 min provided a good logic behind it.

Side notes:
I tried as well using the more powerful PC as Client. This has 2 effects

1. the time until the first reaction on the Client is then much longer,
   
2. the overall time is even longer (See table below)
   

The transfer of the 30MB database itself takes just a few seconds (visible in some short peaks in the task manager).
The transfer of the PDF and MP3 files happen with good speed.

Basically I'm doing all the admin work on a desktop with everything on it, and sync to tablets for plaing. On the tablet(s) I'm only doing fine tuning (like notes, correction of song images, changes to playlists). So if after a session I'd like to sync back my notes then one hour is definitely too much.

About the configuration:

• 2 x Windows 11
  
• Server: i14700 / 128MB / SSD HDD
  
• Typical Client PC: Surface Pro 7 with i10500 / 8MB / SSD HDD
  
• local WLAN speed >100MBit/s (but actually 1GBit LAN is available as well)
  
• Size of mobilesheets.db: 30MB
  
• 6700 songs in the library within 25 PDF files (total about 325 MB, maximum single file 40MB)
  
• MP3 directory (Server) containing ~150k audio files
  
• MP3 directory (Client) containing ~6,5k audio files (35GB of MP3 data)
  
• About 3k songs hold links to audio files (maximum up to 6 links per song)
  
• Server: CPU, network, RAM, HDD load during sync are all low (3-5min 5% the other time 0%)
  
• Client: CPU (~10 to 20%), network (0%), RAM (170 to 320MB), HDD (0%) for about 45min/61min
  

I'm doing a two way sync activating:

• two way sync
  
• add new song
  
• sync media data
  
• sync annotations
  
• sync midi commands
  
• sync groups
  
• sync notes
  
• Audio data: Sync changes and transmit data
  
• Behaviour at merge: Use the latest change
  
• Connect via WIFI
  

---

PC is Master / Tablet is Client
02:49:42  - IP: 192.168.178.36
02:49:51  - Die Datenbank-Datei wird zum verbundenen Gerät gesendet
02:52:29  - 30654464  Bytes der Datei: C:\Users\.....\LocalState\mobilesheets.db erfolgreich gesendet
03:14:22  - Datei erfolgreich empfangen und gespeichert als temp_mobilesheets.db
03:38:55  - Initialisiere Datenbank
03:41:12  - Song XXX wird verglichen
.....
03:44:00  - Die Datenbank-Datei wird zum verbundenen Gerät gesendet
03:44:01  - 14258176  Bytes der Datei: C:\Users\.....\LocalState\temp_mobilesheets.db erfolgreich gesendet
03:44:01  - Datei 1 von 1: C:\Application Data\MobileSheets\xxxx.pdf wird gesendet
03:44:01  - 8309166  Bytes der Datei: C:\Application Data\MobileSheets\xxxx.pdf erfolgreich gesendet
03:44:02  - Die Bibliotheken wurden erfolgreich zusammengeführt. Warten bis der Client fertig ist.
03:44:02  - Die Bibliotheken wurden erfolgreich synchronisiert.
03:44:06  - IP: 192.168.178.36

---

Comparison table with different Master / Client configuration (with start times after last interaction)
Tablet Master  |  PC Master  |  Log
00:00:00  |  00:00:00  |  IP: 192.168.178.21
00:00:16  |  00:00:09  |  Die Datenbank Datei wird zum verbundenen Gerät gesendet
00:19:27  |  00:02:38  |  xxx  Bytes der Datei: C:\Users\...\mobilesheets.db erfolgreich gesendet
00:02:36  |  00:21:53  |  Datei erfolgreich empfangen und gespeichert als temp_mobilesheets.db
00:02:20  |  00:24:33  |  Initialisiere Datenbank
00:42:22  |  00:02:17  |  Song XXX wird verglichen
~0:15:00  |  ~00:05:00 |  End of Song, PDF and other compares (estimated... see Note)
-------------------------------------------------------------------------------------------------
01:07:01  |  00:51:30  |  TOTAL (without song compare)
01:22:01  |  00:56:30  |  TOTAL (with song compare)

Note: The song compare depends on the number of song changes, MP3 changes and the speed of the CPU and SSD of the master. But as there are user interactions required (at date missmatches) a direct compare is not possible, therefore the estimations

---

I'm aware, that for a music library this amount of data is big, but for SQL this is just a tiny library. Questions:

• Can specific settings improve the timing?
  
• Could the more powerful PC (Client or Master) do the main part of the compare job?
  

Comments are welcome.
Best regards Thomas

[Zubersoft]

At the beginning of the sync, a couple of things happen:

1) MobileSheets goes through and reads out all of the data for every song in the entire library. This just means pulling a large amount of data out of the database with various queries.
2) For every file in every song, MobileSheets checks the hash stored in the database as well as the last modified timestamp. If the last modified timestamp stored in the database does not match the last modified timestamp of the file on disk, or if the hash code is 0, the hash is recalculated and the database is updated with the new timestamp. Calculating the hashes for thousands of files can take quite some time, but this would only be necessary if the timestamps for every file did not match up for some reason, or if no previous hashes were stored in the database. I should note that both PDFs and audio files are checked in this manner.

Is something causing the last modified timestamps of all your files to change? Are you making modifications outside MobileSheets? Also, in general, I would not recommend using a two-way sync as it doesn't properly handle deletions (if you delete something on one device, then do a two-way sync, it will be recreated as it sees that one device has something that the other does not). So try to make changes on one device at a time and use a one-way sync.

I have synchronized libraries that are many gigabytes in size, and the whole process took around 15-30 seconds over WiFi if there are no significant changes to process. It definitely should not be taking you 60 minutes every time. Also, if you complete the sync, then just turn around and sync again immediately, does it take the same amount of time or does it finish very quickly?

Mike

[mdavej]

My experience is pretty much the same as the OP. In fact my sync fails most of the time because my tablet will go to sleep. I have to babysit for an hour to make it work. Progress bar shows nothing until close to the end. Happy to share my database analysis. Just let me know.

[Zubersoft]

Without access to a library that replicates the problem, the only other option is to wait until I have time to add extensive logging so that users can generate log files for me that I can investigate. If either of you wants to share two .msb files (one for each device) that exhibits the problem, I can certainly look into it using that, otherwise it may be a while, because, like I said, I don't see the issue even with libraries that are several gigabytes in size. I should also mention that, if you use your own storage location for MobileSheets, this may slow down file I/O due to how Microsoft handles file permissions with UWP applications. I'm able to implement some optimizations if the default storage location is used to speed up certain file operations, but these optimizations don't work if users choose their own folder to manage things. There is nothing that can be done about that until I switch the Windows version of MobileSheets to a different framework (which is something I am considering either later this year or next year).

Mike

[TKW]

Hi, thank you for the feedback and explaination. I can make a log as requested. And yes doing the same syn. twice  results in the same time again. I observed as well time/date missmatch of MP3 files even after the first sync. This is especially strange as the MP3 files are always untouched. I simply generated them at first generation of the song entries but never touch them so far.
Regarding storage location. Yes I put the PDFs and MP3 files on a seperate flash disk due to memory limitations on the main SSD. But even that should be fairly fast. I'll double check the speed. But even that can only accouunt for the tablet side. On the desktop side the location is as well not the default but VERY fast.
If you can mail me directly where to send what, then I'll do it the next days.
Thanks in advance.
Thomas

[Zubersoft]

The logging isn't in place yet. I haven't had time to work on that. Also, it's not about the speed of the storage - it's about the speed of the API that accesses that storage. UWP applications are forced to use Microsoft's StorageFile and StorageFolder classes which are inherently slow for accessing files. There are optimizations I can take to get around that with the default storage location, but not with a custom one, and this only applies to some things.

Mike

[TKW]

Hi Mike,
Understood. Just let me know when you are prepared.
Regards Thomas

[TKW]

Hi Mike,

I believe I identified one potential issue for the situation.
The file date of the MP3 files seems to be in mobilesheet database differenct from the original file date. 

In my case, I did do batch import (On my Desktop PC) where I linked the MP3 files in Excel (CSV) to the songs and kept them in the original place by using a directory joint, prior to the import. After the import it seems that the MP3 file entries in the library hold all incorrect NEW dates. 
In effect the files were not even copied as they were already in the right spot at the time I did the CSV import. So no copy was performed as none was needed.

After the first sync between Desktop PC and Tablet the files on the Tablet received new dates while the ones on the PC keep the original dates.

Doing a second sync did not cure the problem. The library seems to hold even a third date which is shown at the conflict release dialog at least for a number of the files (about 20 out of the 6600 MP3 files).

Doing a direct compare of the two MP3 folders between PC and Tablet (using Total Commander - Directory Sync) then I can see that all the files on the Tablet hold NEW dates while all the files on the PC hold the ORIGINAL dates even after several two-way sync transactions.
All in all it sounds like a lot of time stamp fun.

Best regards
Thomas

[Zubersoft]

MobileSheets does not store the file creation date - it stores the last modified timestamp. If you are seeing that the last modified timestamp doesn't match, that would certainly be a problem and I'll have to look into that. On older Android devices, it used to be possible to change the last modified timestamp and force it to match the database. It's no longer possible to do that on Android and it's not possible at all on Windows and iOS. There are more places in the code where it checks the last modified timestamp of PDFs and other files to look for changes and updates the database when that occurs. This is not the case with audio files, so that may be part of the problem. 

Thanks,
Mike

[TKW]

Hi Mike,
I did a bit more research on the time matter:

On my desktop PC and tablet the time of the MP3 files and the entries in the database are by 1 respectively 2 hours offset and do not match even after two-way sync. In addition some files hold a similar date, while others hold completely different dates between the two devices. Three examples:
a child is born
Database PC:        2024-04-25 18:37:13.643
Filesystem PC:       2024-04-25 20:37:13
Filesystem Tablet:  2024-03-28 12:27:34
Database Tablet:   2024-03-28 11:27:34.0

1999
Database PC:        2024-04-06 18:53:58.634
Filesystem PC:       2024-04-06 20:53:58
Filesystem Tablet:  2024-04-06 20:54:12
Database Tablet:   2024-04-06 18:54:12.0

Honey Pie
Database PC:        2022-01-13 16:26:02.77
Filesystem PC:       2022-01-13 17:26:02
Filesystem Tablet:  2024-03-28 15:28:50
Database Tablet:   2024-03-28 14:28:50.0

Both devices are in the same time zone and hold the correct and same system time (at least to the minute level which is displayed by Windows).
The database time was retrieved by SQLite formatiing the date as Java-Epoche (ms).
The filesysystem time is the last changed date retrieved using total commander attributes.

Regards Thomas

[TKW]

One more idea. The one versus two hours is probabaly summer versus winter time.

[Zubersoft]

I'm going to review the code, because now that I store the hash of every file in the database, it shouldn't be necessary to even check the last modified timestamp. I believe I recall removing that check for PDFs and other song files. I'll just have to verify that I also removed it for audio files. If not, I'll just get rid of that, so that only hashes are compared when trying to determine if files have changed at all. If I already removed those changes, then the last modified timestamp may not even be referenced any longer, so it shouldn't matter if there are differences.

Mike

[TKW]

I'm going to review the code, because now that I store the hash of every file in the database, it shouldn't be necessary to even check the last modified timestamp. I believe I recall removing that check for PDFs and other song files. I'll just have to verify that I also removed it for audio files. If not, I'll just get rid of that, so that only hashes are compared when trying to determine if files have changed at all. If I already removed those changes, then the last modified timestamp may not even be referenced any longer, so it shouldn't matter if there are differences.

Mike

Hi Mike,
that sounds promissing. I'm looking forward to your results.
Thomas