Unnecessary DELETE/INSERT queries on $ext tables during APP install

[sgiordy]

1. Describe the bug During APP install or data upgrade phase, unnecessary queries are executed on database to assert that there is no orphans between principal table and its "$ext" companion. These queries are executed also if the target table is not modified, installing the APP via VSCode or via Powershell, publishing with RAD or not. Seems that there is no Application Server option to disallow this behaviour.

The issue is critical because in database with millions and millions of records, an APP upgrade can took several minutes or hours, with unnecessary CPU load.

2. To Reproduce In OnPrem environment, open SQL Server Profiler.

Publish and install an APP with at least one table extension.

You log these queries, for example:

DELETE FROM "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$ext" WHERE NOT EXISTS (SELECT 1 FROM "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972" WHERE "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$ext"."No_" = "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972"."No_")

INSERT INTO "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$ext" ("No_", ...) SELECT "No_",N'',0,N'',N'',0.0 FROM "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972" (SERIALIZABLE) WHERE NOT EXISTS (SELECT 1 FROM "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$ext" WHERE "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$ext"."No_" = "Demo Database BC (23-0)".dbo."CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972"."No_")

3. Expected behavior These queries must be executed only if the companion table is changed, not always!

4. Actual behavior Unnecessary queries on unchanged tables.

5. Versions:

• AL Language: 12
• Visual Studio Code: 2023-11-6, 15:55:43
• Business Central: OnPrem V23 RTM IT

[BazookaMusic]

I have logged a bug for the relevant team and they will be looking at it, it seems that this is an issue on the runtime. I have also asked the team to update this issue when they look at it.

However, this repo is technically not correct for this request, since it is a runtime issue and thus I will close this issue. I would suggest to open a support case for it, since then it will be picked up by the appropriate team.

You can open Support Request to CSS through PartnerSource portal or contact your Service Account Manager (SAM) in the local subsidiary to understand what is included in your contract as of support incident and PAH (Partner Advisory Hours). Your SAM might also step by step direct you how to open a support request or how to get credentials if this is the first time for you / your company.

[sgiordy]

Thanks @BazookaMusic very much for asking, the last time I opened a case through PartnerSource the support team asked me for 1 TB of SQL backup to reproduce the issue :) so I thought that this problem also affects SaaS environments.

[jehelles]

This is a known issue that is not new with 23.x. There is a plan to improve on this, but I unfortunately cannot give an ETA on when this will be delivered.

[sgiordy]

Thank you @jehelles, I searched for similar previous issues but I didn't find anything. Now with V23 is really possibile to migrate huge (TB) databases... so this "little" issue has a very big impact on system downtime (dozens of minutes), due unnecessary queries.