Removing stored procedures

[Mimetis]

I think I will remove all the stored procedures (in SQL / MYSQL) in a future version.

Many reasons for that:

• A lot of people are "playing" with the Setup even after a first sync, that means DMS has to take care of already created stored procedures. Quite complicated
• Today the SQL engine performances between generated SQL and stored procedures are almost the same
• Generating the SQL commands on the fly will be easier to handle common changes on the Setup object
• On MySQL it could increase the performances (mainly because the stored proc is called on every lines...

Pitfalls

• Performances should not be an issue, but it could be in some circumstances
• I know some users are "editing" the stored procedures manually, even if they are generated again, over time...
• DMS in memory cache will increase a little bit, but not too much
• Already in place sync systems will still have the stored proc, but they will not be used with the new version

Your thought ?

[gentledepp]

Please make this an option! We will definitively stick with stored procedures and handle any schema changes ourselves, as it absolutely has a performance impact.

[Mimetis]

@gentledepp
I'm trying to remove stored procedures, and the first tests I've made seems to be pretty good.
The version without Stored procedure is 2% less performant, over 1M rows (if we use correctly the Prepare() method ;) )

[gentledepp]

Yes, but if you do not use stored procedures, you will likely use dynamic sql (or what Entity Framework uses: sp_exec(''))
If you have complex queries (we have considerably more complex queries than what is generated by DMS out of the box), this can have a considerable performance impact.

So please at least give us a chance to stick with the good-old pattern. 😅

As a side-note: I am currently trying to bring some of our requested features to DMS in our separate repo and tracking these as our own issues: https://github.com/gentledepp/Dotmim.Sync

Additionally, stored procedures give us the ability to make major changes to how DMS syncs.
We do this by:

• running an integration test that creates all stored procedures using DMS and extract the SQL
• modifying the SQL and apply the triggers and stored procedures using an entity framework DbMigration

That way, we have the whole sync process under control and are most flexible.

How would this be done without stored procedures?

[Mimetis]

Oh god @gentledepp, I hate you 🗡️ !!!!
Ok, I will let the stored procedures, but it will be complicated to maintain :)
Do you allow me to change a little bit how it's working ?

For instance I would want to add the scopename in it, so far, we can make a smooth upgrade when required.
Let's say, when upgrade to V1 to V2, using scopes (with new columns for example) we can have two kind of stored proc:

We have today a stored proc called [dbo].[Customer_changes], that will become:

• [dbo].[Customer_v1_changes] for scope v1 that will query columns Name, City
• [dbo].[Customer_v2_changes] for scope v2 that will query columns Name, City, NewColumn

Is it something that could help for migration ?

[gentledepp]

1st of all: I cannot allow you anything since you are the owner ;-)
2nd: I just give you feedback, as we have a lot of experience running DMS on several continents now.

3rd: I do not quite get the V1 to V2 scenario.
What issue is it you want to achieve? Do you want to allow DMS developers to maintain multiple sync scope definitions, so that they can include an additional column without having to re-provision everything? 😅

Automatic scope-migration is something that I plan to implement myself, but it will only support adding nullable columns and adding new tables

[Mimetis]

The idea is to have different scopes for different version.

Let's say you are creating the scope v1 with some tables and columns
All clients have this scope and they are syncing to this scope from their application

You decide to add new columns and new tables
For that you're creating a new scope v2 with this new table and new columns
you are deploying a new version of the application to some client that will use the scope v2 and test the new version
Meanwhile, the others client, still on v1, will continue to use the v1 scope

That means I need two version of the stored proc (for v1 scope and for v2)

Make sense ?

[gentledepp]

Yes it does, but you can already do this using custom stored procedure pre- and postfixes, right?

[Mimetis]

I think it will remove the older stored proc to rename them.
I need to check this behavior

It seems to me that it's making more sense to base versioning and upgrade on scope name, right ?

[gentledepp]

Hmmm... well it depends on what exactly you want to change.

• if you have a scope with 5 tables and you add a single column to one of them, creating new stored procedures for the remaining 4 tables seems like an overkill to me.

I would rather follow the following approach:

• support automatic migrations for backward-compatible scope changes (adding table, adding column)
• In case backward-incompatible changes are required, allow developers to create a new scope for v2 clients.

Does that make sense to you?

[Mimetis]

• if you have a scope with 5 tables and you add a single column to one of them, creating new stored procedures for the remaining 4 tables seems like an overkill to me.

That why I wanted to remove the stored procedures actually.

• support automatic migrations for backward-compatible scope changes (adding table, adding column)

Today if you're adding a new column in a table (nullable), it will still work for v1 I guess, since the stored procedure is not affected by the new column, but the v2 will not work.
Then, you are adding this new column to the Setup object, and the stored procedure will be regenerated.
The v2 clients will work correctly
The v1 clients will break

• In case backward-incompatible changes are required, allow developers to create a new scope for v2 clients.

That's the main purpose of scopes. Making the schema evolving and smoothly migrate the clients.
But for maintaining a backward compatibility, I need to:

• Versioning the stored procedures (by scope) to be sure v1 will continue to work and v2 will work as well
• OR Remove them and generate the commands in memory (and taking care of the perfs) to be sure v1 and v2 work side by side

Are you heavily modifying the stored procedures once created ?
Which one are you modifying ? (Customer_changes, Customer_bulkupdate, Customer_initialize ...) ?

[gentledepp]

The v2 clients will work correctly
The v1 clients will break

Well, my idea is basically, to modify dotmim.sync so that schema/scope changes (adding nullable columns, adding tables) does not break any clients. Instead, DMS should just add the new tables/columns on the client-side.
I have a protocol in mind, but I need more time to develop this.

But this would be able to evolve like a REST API:

• if you use message objects and only add endpoints and/or additional properties to those messages, existing clients won't break
• in case you need to introduce a breaking change, you must introduce a new scope and create new clients that do support it

As a client app developer, the only thing you'd need to make sure is that you never use select * from customers, but always specify the inserted/updated columns explicitly. But as you usually use an ORM like EF Core or SQLite-Net, this should not be too hard to achieve.

Are you heavily modifying the stored procedures once created?

We needed to modify the following 3 areas:

1. the selectchanges stored procedure that includes our filter parameters (UserIdTenantId_selectchanges). This is because we implemented our own sync-filter approach that allows us to sync a different set of rows for each client in a very flexible manner
2. the bulkinsert stored procedures because of a bug (latest version of DMS does not have this bug anymore)
3. some of the triggers as we only wanted to track a change if any of the columns changed that actually are synchronized (for supporting a legacy sql schema)

[champcbg]

@gentledepp I am interested in what you stated about extracting the SQL and letting EF MIgrations handle the adding of the triggers and sprocs.

I am using EF also, on server against SQL Server and mobile against Sqlite.

this process might remove some steps i have now, the mobile client does a check to see if it needs to be updated or if it can sync before every sync. This way the clients now when they need to update which is Deprovision , EF Migration, then Provision.

Sounds like with ur solution, migration can be called at app (web and mobile) start up.

[champcbg]

@Mimetis I agree with @gentledepp having the option to have dynamic sproc would be my preferred option of choice.

[xyun52]

how to disable stored procedures