Newsequentialid on Sql Server and DotMim.Sync

[needledrag]

First off thanks for a great framework for syncing, I haven't found anything out there as feature rich.

Jut thought I'd see if anyone has any thoughts on the following design decision :

I started using the sync framework with 2 scopes, one to pull (download) all required data and one to push data (upload) from the clients to the server (around 4-5k clients).
I'm using guids for primary keys and the keys are generated on the client and at certain intervals all the data added on the client is synced to the sever.
A clients' data is specific to that client so they're only ever adding records that they'll have access to in the future.
So they can work offline and make numerous inserts on their app (mobile client) without having to wait for server responses and when the sync is called it uploads their data.
Because I'm generating guids on the client for my primary key I can't take advantage of Sql Servers NEWSEQUENTIALID() function which apparently causes less page fragmentation and results in faster access via the primary key.
I'm a little concerned that as the system grows with more records I'd have issues with speed and server resources. Is this an over reaction?

I'm contemplating using web api calls for all the client inserts/updates and then mainly use the sync framework for downloading data. In which case I could have an identity column, add a record and return it to the client to insert.
Problem is I'd lose my ability to work offline if the client needs to add a record. Also the app captures a lot of data and making all the web api calls results in a slower user experience when using the app, it seems I'd lose a lot of goodness using web api calls. Id still have a scope for pulling data but would probably only use it for reinitialsing a client with their data if they change phones/tablets etc...

Has anyone experienced any server database problems with guids as primary keys eg speed and space?
Is there any way to take advantage of NEWSEQUENTIALID() when using DotMim.Sync?

Thanks in advance

[gentledepp]

The issue is less using newsequentialid() on sqlserver, as there will likely only be one, right?
The issue is rather, that phones and tablets run sqlite only, which does not have anything like that.

There are mainly two downsides of using GUIDs as primary keys.

1. the indexes will be fragmented and therefore not perform as great as a bigint-based index. Nothing you can really do here
2. Insert speed may degrade signifficantly, if you use GUIDs as clustered index. (Which is what you normally do, when using them as primary key).

Now, why is that (2) you may ask? The reason is that the clustered index affects where rows are stored on disk. So if the guids are not added sequentially, sql server will have to re-arrange the partitions on disk regularly, which degrades the insert speed. (due to higher disk usage)

To overcome this, sequential guids may be used.
On sqlite side, we found that we can get away by creating our sequential guid in C# (and not in sqlite sql code)

In that case, there is an interesting library, which mimics the way Mongodb creates sequential guids. (Mongodb is a good rolemodel, as it is a databse that supports clustering and sharding out of the box)

So check out this library:
https://github.com/buvinghausen/SequentialGuid

Disclaimer: I have not used it yet, but the foundation looks rock solid.

I hope that helped!

[needledrag]

Thanks for the link, it definitely looks interesting, I read you discussion with the dev, it seems we're looking at a similar situation.
It may be a way forward if the machine name issue can be resolved for mobile clients?

Another thing I've considered (and read about) is just keeping things the way they are and adding a bigint IDENITITY() column to each table and making this the clustered index instead of the guid primary key.
If the client(user) app has knowledge of this value and sends it as a parameter in the sync, other table records would have a link to the user on which to join that isn't a guid and this should prevent performance issues related to guids as clustered indexes? I'd have to look at security implications as guids are pretty difficult to guess as opposed to identity values but there's security to only allow users access to records matching their credentials. Not sure if it's worth changing so much of the schema but I guess I'll have to test it out.

Thank you for your help.

[VagueGit]

Would a sequential guid solve the problem for multiple clients? Each guid generated on a client might be sequential for that client on that device, but would sequential guids generated by multiple clients on multiple devices be sequential relative to each other?

[Mimetis]

I don't think so

[Mimetis]

I started using the sync framework with 2 scopes, one to pull (download) all required data and one to push data (upload) from the clients to the server (around 4-5k clients).

:o :o :o !!!!!

You know this framework has not even reach the V1 ? :D

You should be crazy :D (And I love it !!!)

Anyway, on your Guids problems:

On SQL Server, you need to Reorganize your indexes on a week / daily basics timeframe.
Especially because of fragmentation.

To mitigate the fragmentation, you need also to create your table with a correct Fill Factor on your cluster index, to get more spaces in each row pages, and having the opportunity to fragment less. It will have an impact on Select speed and disk usage, but it's always worth it.

Using an incremental value (like sequential guid or identity int) does not require Fill Factor

When I was instructor, I usually use to say to my students that a good Fill Factor for any table with Guids could be around 70% ~ 80%
That way you have 20~30% of space on each cluster index to allow insertion of Guids.

That being said, as suggest @gentledepp, using a NEWSEQUENTIALID on Sql Server and a Custom Sequential Guid on SQLite can be worth it in your case (I did not tested it, so not sure it can work, if you test this solution, let us know !).
In parallel with a good Fill Factor (and a Reorganize strategy) you should have better performances

By the way, where are the bottlenecks in your case ? on the clients ? on the server ?
Do you know what causes the performances issue ? Some Select statements ? Some Inserts ? Deadlocks ?

[needledrag]

:) I'm not in full production mode yet, still testing but have an informal rollout to certain people in the industry, less than a 10th of the users (mobile clients) do most of the uploading of data, the rest have a light version of the app and consume the data uploaded that pertains to them. But your framework has worked really well.

To be honest I haven't had any issues with performance, I did have the sync as a background job running every 30 min or so but now run it at various stages in the app where the data needs to be synced (uploaded) before the processes can happen eg a customer of the user needs to fill a specific form via email link etc.. mainly because it wouldn't help being able to work offline at that point, the server needs the information before anything else can happen. This could occur between 1, 5, or 20 times a day depending on how busy they are and anywhere from 1 to potentially 50 records per upload sync. All the data that is downloaded via sync uses bigint identity primary keys and doesn't change often so it's runs every 4 hours if the user is using the app, otherwise at logon.

The reason I'm asking about performance and fragmentation issues is because it was mentioned by dba friend as something to watch out for as eventually in production there would be a lot more users (and clients of those users using the light version of the app) depending on the adoption of the project. Apparently even NEWSEQUENTIALID has some fragmentation issues although nowhere near as much as NEWID.
Thanks for your advice on the Fill Factor, this triggered my memory from ages ago but I haven't had to deal with fine tuning indexes for years, I'll be going along these lines for sure as well as scheduled index maintenance.

Didn't know about git sponsors, will definitely do so, thank you.

[Mimetis]

Didn't know about git sponsors, will definitely do so, thank you.

Thank you so much !

[VagueGit]

With 5000 users would UtcTicks offer a sufficiently robust and performant solution? All inserts would be sequential and the likelihood of collisions is infinitesimal.

I assume I've missed something obvious.

[needledrag]

In my case I'm not sure that would work. In one area of the app the user selects a number of options which are inserted into the client table in a loop, it's possible this could potentially cause duplicates of UTC ticks which would fail on the insert. I think a guid has a higher probability of being unique in this case. Not a bad shout if you're using composite indexes though.