Waiting for the 2023 Wave 2 release of Dynamics 365 BC, I want to share some considerations with you about the Extensions data model.
Here is what we are expecting from Microsoft:
This release focuses on optimizing an essential part of the data stack in the Business Central server: The data model for table extensions.
In previous releases, when a developer extended a table, the fields from the table extension were stored in a separate table called a companion table in the database. This design was chosen to make the app lifecycle (install, update, uninstall) nondisruptive to normal usage of the base table. Unfortunately, it also had impact on the runtime performance on the system because the Business Central server needed to join data from table extensions when reading data from a table. As an example, for a table with 7 table extensions, the server might need to do a 7-way join.
In this release, we change the data model for table extensions so that added fields from all extensions to a table are now stored in the same companion table. In this new model, the server will never need to do more than a single join of the base table to its companion table. We expect this to drastically reduce the performance impact of table extensions to base tables.
It sounds good! One join it’s certainly better than N joins.
Who played with SQL Profiler has logged these kind of queries that are “server-killing” in huge environments with hundreds of users.
SELECT TOP (1) "Customer"."timestamp" AS "timestamp","Customer"."No_" AS "No_","Customer"."Name" AS "Name","Customer"."Search Name" AS "Search Name","Customer"."Name 2" AS "Name 2","Customer"."Address" AS "Address","Customer"."Address 2" AS "Address 2","Customer"."City" AS "City","Customer"."Contact" AS "Contact","Customer"."Phone No_" AS "Phone No_","Customer"."Telex No_" AS "Telex No_","Customer"."Document Sending Profile" AS "Document Sending Profile","Customer"."Ship-to Code" AS "Ship-to Code","Customer"."Our Account No_" AS "Our Account No_","Customer"."Territory Code" AS "Territory Code","Customer"."Global Dimension 1 Code" AS "Global Dimension 1 Code","Customer"."Global Dimension 2 Code" AS "Global Dimension 2 Code","Customer"."Chain Name" AS "Chain Name","Customer"."Budgeted Amount" AS "Budgeted Amount","Customer"."Credit Limit (LCY)" AS "Credit Limit (LCY)","Customer"."Customer Posting Group" AS "Customer Posting Group","Customer"."Currency Code" AS "Currency Code","Customer"."Customer Price Group" AS "Customer Price Group","Customer"."Language Code" AS "Language Code","Customer"."Statistics Group" AS "Statistics Group","Customer"."Payment Terms Code" AS "Payment Terms Code","Customer"."Fin_ Charge Terms Code" AS "Fin_ Charge Terms Code","Customer"."Salesperson Code" AS "Salesperson Code","Customer"."Shipment Method Code" AS "Shipment Method Code","Customer"."Shipping Agent Code" AS "Shipping Agent Code","Customer"."Place of Export" AS "Place of Export","Customer"."Invoice Disc_ Code" AS "Invoice Disc_ Code","Customer"."Customer Disc_ Group" AS "Customer Disc_ Group","Customer"."Country_Region Code" AS "Country_Region Code","Customer"."Collection Method" AS "Collection Method","Customer"."Amount" AS "Amount","Customer"."Blocked" AS "Blocked","Customer"."Invoice Copies" AS "Invoice Copies","Customer"."Last Statement No_" AS "Last Statement No_","Customer"."Print Statements" AS "Print Statements","Customer"."Bill-to Customer No_" AS "Bill-to Customer No_","Customer"."Priority" AS "Priority","Customer"."Payment Method Code" AS "Payment Method Code","Customer"."Last Modified Date Time" AS "Last Modified Date Time","Customer"."Last Date Modified" AS "Last Date Modified","Customer"."Application Method" AS "Application Method","Customer"."Prices Including VAT" AS "Prices Including VAT","Customer"."Location Code" AS "Location Code","Customer"."Fax No_" AS "Fax No_","Customer"."Telex Answer Back" AS "Telex Answer Back","Customer"."VAT Registration No_" AS "VAT Registration No_","Customer"."Combine Shipments" AS "Combine Shipments","Customer"."Gen_ Bus_ Posting Group" AS "Gen_ Bus_ Posting Group","Customer"."GLN" AS "GLN","Customer"."Post Code" AS "Post Code","Customer"."County" AS "County","Customer"."EORI Number" AS "EORI Number","Customer"."Use GLN in Electronic Document" AS "Use GLN in Electronic Document","Customer"."E-Mail" AS "E-Mail","Customer"."Home Page" AS "Home Page","Customer"."Reminder Terms Code" AS "Reminder Terms Code","Customer"."No_ Series" AS "No_ Series","Customer"."Tax Area Code" AS "Tax Area Code","Customer"."Tax Liable" AS "Tax Liable","Customer"."VAT Bus_ Posting Group" AS "VAT Bus_ Posting Group","Customer"."Reserve" AS "Reserve","Customer"."Block Payment Tolerance" AS "Block Payment Tolerance","Customer"."IC Partner Code" AS "IC Partner Code","Customer"."Prepayment _" AS "Prepayment _","Customer"."Partner Type" AS "Partner Type","Customer"."Intrastat Partner Type" AS "Intrastat Partner Type","Customer"."Image" AS "Image","Customer"."Privacy Blocked" AS "Privacy Blocked","Customer"."Disable Search by Name" AS "Disable Search by Name","Customer"."Preferred Bank Account Code" AS "Preferred Bank Account Code","Customer"."Coupled to CRM" AS "Coupled to CRM","Customer"."Cash Flow Payment Terms Code" AS "Cash Flow Payment Terms Code","Customer"."Primary Contact No_" AS "Primary Contact No_","Customer"."Contact Type" AS "Contact Type","Customer"."Mobile Phone No_" AS "Mobile Phone No_","Customer"."Responsibility Center" AS "Responsibility Center","Customer"."Shipping Advice" AS "Shipping Advice","Customer"."Shipping Time" AS "Shipping Time","Customer"."Shipping Agent Service Code" AS "Shipping Agent Service Code","Customer"."Service Zone Code" AS "Service Zone Code","Customer"."Price Calculation Method" AS "Price Calculation Method","Customer"."Allow Line Disc_" AS "Allow Line Disc_","Customer"."Base Calendar Code" AS "Base Calendar Code","Customer"."Copy Sell-to Addr_ to Qte From" AS "Copy Sell-to Addr_ to Qte From","Customer"."Validate EU Vat Reg_ No_" AS "Validate EU Vat Reg_ No_","Customer"."Id" AS "Id","Customer"."Currency Id" AS "Currency Id","Customer"."Payment Terms Id" AS "Payment Terms Id","Customer"."Shipment Method Id" AS "Shipment Method Id","Customer"."Payment Method Id" AS "Payment Method Id","Customer"."Tax Area ID" AS "Tax Area ID","Customer"."Contact ID" AS "Contact ID","Customer"."Contact Graph Id" AS "Contact Graph Id","Customer"."Int_ on Arrears Code" AS "Int_ on Arrears Code","Customer"."Fiscal Code" AS "Fiscal Code","Customer"."Individual Person" AS "Individual Person","Customer"."Resident" AS "Resident","Customer"."First Name" AS "First Name","Customer"."Last Name" AS "Last Name","Customer"."Date of Birth" AS "Date of Birth","Customer"."Tax Representative Type" AS "Tax Representative Type","Customer"."Tax Representative No_" AS "Tax Representative No_","Customer"."Place of Birth" AS "Place of Birth","Customer"."Cumulative Bank Receipts" AS "Cumulative Bank Receipts","Customer"."PA Code" AS "PA Code","Customer"."PEC E-Mail Address" AS "PEC E-Mail Address","Customer_e1"."PFX Company Bank Account" AS "PFX Company Bank Account","Customer_e1"."PFX Shipment Copies" AS "PFX Shipment Copies","Customer_e1"."PFX Net Prices (Print)" AS "PFX Net Prices (Print)","Customer_e1"."PFX Invoice Group" AS "PFX Invoice Group","Customer_e1"."PFX Mobile Phone No_" AS "PFX Mobile Phone No_","Customer_e1"."PFX Certified E-Mail" AS "PFX Certified E-Mail","Customer_e1"."PFX Balance Vendor No_" AS "PFX Balance Vendor No_","Customer_e1"."PFX Yours Account No_" AS "PFX Yours Account No_","Customer_e1"."PFX Partner Type" AS "PFX Partner Type","Customer_e1"."PFX Feature 1" AS "PFX Feature 1","Customer_e1"."PFX Feature 2" AS "PFX Feature 2","Customer_e1"."PFX Feature 3" AS "PFX Feature 3","Customer_e1"."PFX Feature 4" AS "PFX Feature 4","Customer_e1"."PFX Feature 5" AS "PFX Feature 5","Customer_e1"."PFX Latitude" AS "PFX Latitude","Customer_e1"."PFX Longitude" AS "PFX Longitude","Customer_e1"."PFX Phone No_ 2" AS "PFX Phone No_ 2","Customer_e1"."PFX DUNS No_" AS "PFX DUNS No_","Customer_e1"."PFX Block Reason Code" AS "PFX Block Reason Code","Customer_e1"."PFX Manual Block" AS "PFX Manual Block","Customer_e1"."PFX Order Type" AS "PFX Order Type","Customer_e1"."PFX Transport Reason Code" AS "PFX Transport Reason Code","Customer_e4"."PFX SdI Job" AS "PFX SdI Job","Customer_e4"."PFX Non-Resident Country Code" AS "PFX Non-Resident Country Code","Customer_e4"."PFX SdI Format" AS "PFX SdI Format","Customer_e4"."PFX SdI Delivery" AS "PFX SdI Delivery","Customer_e4"."PFX SdI Address" AS "PFX SdI Address","Customer_e4"."PFX Transport Reason Code" AS "PFX Transport Reason Code","Customer_e4"."PFX Operation Type" AS "PFX Operation Type","Customer_e4"."PFX Default Withh_ Tax Code" AS "PFX Default Withh_ Tax Code","Customer_e4"."PFX Dish_ Reminder Terms Code" AS "PFX Dish_ Reminder Terms Code","Customer_e4"."PFX RID Code" AS "PFX RID Code","Customer_e4"."PFX Notice Terms Code" AS "PFX Notice Terms Code","Customer_e4"."PFX Default Soc_ Sec_ Code" AS "PFX Default Soc_ Sec_ Code","Customer_e13"."PFX Document Price Visible" AS "PFX Document Price Visible","Customer_e13"."PFX Line of Shipment Group" AS "PFX Line of Shipment Group","Customer_e13"."PFX Allow Invoice from Order" AS "PFX Allow Invoice from Order","Customer_e13"."PFX Order grouping" AS "PFX Order grouping","Customer_e13"."PFX Whse_ Note" AS "PFX Whse_ Note","Customer_e9"."PFX Our Bank DocFinance" AS "PFX Our Bank DocFinance","Customer_e9"."PFX Derogation Code" AS "PFX Derogation Code","Customer_e9"."PFX Financial Item" AS "PFX Financial Item","Customer_e9"."PFX Avg_ Days Pymt_Delay Hist_" AS "PFX Avg_ Days Pymt_Delay Hist_","Customer_e9"."PFX Avg_ Days Pymt_Delay P_Y_" AS "PFX Avg_ Days Pymt_Delay P_Y_","Customer_e9"."PFX Avg_ Days Pymt_Delay C_Y_" AS "PFX Avg_ Days Pymt_Delay C_Y_","Customer_e9"."PFX Avg_ Days Data Update" AS "PFX Avg_ Days Data Update","Customer_e9"."PFX Direct Remittance Code" AS "PFX Direct Remittance Code","Customer_e12"."PFX
Health Tracking Type" AS "PFX Health Tracking Type","Customer_e12"."PFX Health Tracking Code" AS "PFX Health Tracking Code","Customer_e6"."PFX Customer Group" AS "PFX Customer
Group","Customer_e6"."PFX _ Dishonored" AS "PFX _ Dishonored","Customer_e6"."PFX Next Remind Date" AS "PFX Next Remind Date","Customer"."$systemId" AS "$systemId","Customer"."$systemCreatedAt" AS
"SystemCreatedAt","Customer"."$systemCreatedBy" AS "SystemCreatedBy","Customer"."$systemModifiedAt" AS "SystemModifiedAt","Customer"."$systemModifiedBy" AS "SystemModifiedBy",ISNULL("SUB$No_ of
Quotes"."$CNT",0) AS "No_ of Quotes",ISNULL("SUB$No_ of Blanket Orders"."$CNT",0) AS "No_ of Blanket Orders",ISNULL("SUB$No_ of Orders"."$CNT",0) AS "No_ of Orders",ISNULL("SUB$No_ of
Invoices"."$CNT",0) AS "No_ of Invoices",ISNULL("SUB$No_ of Return Orders"."$CNT",0) AS "No_ of Return Orders",ISNULL("SUB$No_ of Credit Memos"."$CNT",0) AS "No_ of Credit Memos",ISNULL("SUB$No_
of Pstd_ Shipments"."$CNT",0) AS "No_ of Pstd_ Shipments",ISNULL("SUB$No_ of Pstd_ Invoices"."$CNT",0) AS "No_ of Pstd_ Invoices",ISNULL("SUB$No_ of Pstd_ Return Receipts"."$CNT",0) AS "No_ of
Pstd_ Return Receipts",ISNULL("SUB$No_ of Pstd_ Credit Memos"."$CNT",0) AS "No_ of Pstd_ Credit Memos",ISNULL("SUB$PFX Repayment Plans"."$CNT",0) AS "PFX Repayment Plans",ISNULL("SUB$Finance
Charge Memo Amounts"."Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry$SUM$Amount",0.0) AS "Finance Charge Memo Amounts" FROM
"MyDatabase".dbo."MyCompany$Customer$437dbf0e-0000..." AS "Customer" WITH(READUNCOMMITTED) JOIN "MyDatabase".dbo."MyCompany$Customer$6be88df7-0000..."
"Customer_e1" WITH(READUNCOMMITTED) ON ("Customer"."No_" = "Customer_e1"."No_") JOIN "MyDatabase".dbo."MyCompany$Customer$f09234c0-0000..." "Customer_e4"
WITH(READUNCOMMITTED) ON ("Customer"."No_" = "Customer_e4"."No_") JOIN "MyDatabase".dbo."MyCompany$Customer$43a5ffbb-0000..." "Customer_e13" WITH(READUNCOMMITTED) ON ("Customer"."No_" = "Customer_e13"."No_") JOIN "MyDatabase".dbo."MyCompany$Customer$8bc28e52-0000..." "Customer_e9" WITH(READUNCOMMITTED) ON ("Customer"."No_" = "Customer_e9"."No_") JOIN "MyDatabase".dbo."MyCompany$Customer$c0db2716-0000..." "Customer_e12" WITH(READUNCOMMITTED) ON ("Customer"."No_" = "Customer_e12"."No_") JOIN "MyDatabase".dbo."MyCompany$Customer$f72871d9-0000..." "Customer_e6" WITH(READUNCOMMITTED) ON ("Customer"."No_" = "Customer_e6"."No_") OUTER APPLY (SELECT TOP (1) COUNT("No_ of Quotes$Sales Header"."Document Type") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Header$437dbf0e-0000..." AS "No_ of Quotes$Sales Header" WITH(READUNCOMMITTED) WHERE ("No_ of Quotes$Sales Header"."Document Type"=@1 AND "No_ of Quotes$Sales Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Quotes" OUTER APPLY (SELECT TOP (1) COUNT("No_ of Blanket Orders$Sales Header"."Document Type") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Header$437dbf0e-0000..." AS "No_ of Blanket Orders$Sales Header" WITH(READUNCOMMITTED) WHERE ("No_ of Blanket Orders$Sales Header"."Document Type"=@2 AND "No_ of Blanket Orders$Sales Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Blanket Orders" OUTER APPLY (SELECT TOP (1) COUNT("No_ of Orders$Sales Header"."Document Type") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Header$437dbf0e-0000..." AS "No_ of Orders$Sales Header" WITH(READUNCOMMITTED) WHERE ("No_ of Orders$Sales Header"."Document Type"=@3 AND "No_ of Orders$Sales Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Orders" OUTER APPLY (SELECT TOP (1) COUNT("No_ of Invoices$Sales Header"."Document Type") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Header$437dbf0e-0000..." AS "No_ of Invoices$Sales Header" WITH(READUNCOMMITTED) WHERE ("No_ of Invoices$Sales Header"."Document Type"=@4 AND "No_ of Invoices$Sales Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Invoices" OUTER APPLY (SELECT TOP (1) COUNT("No_ of Return Orders$Sales Header"."Document Type") AS
"$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Header$437dbf0e-0000..." AS "No_ of Return Orders$Sales Header" WITH(READUNCOMMITTED) WHERE ("No_ of Return Orders$Sales
Header"."Document Type"=@5 AND "No_ of Return Orders$Sales Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Return Orders" OUTER APPLY (SELECT TOP (1) COUNT("No_ of Credit
Memos$Sales Header"."Document Type") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Header$437dbf0e-0000..." AS "No_ of Credit Memos$Sales Header" WITH(READUNCOMMITTED)
WHERE ("No_ of Credit Memos$Sales Header"."Document Type"=@6 AND "No_ of Credit Memos$Sales Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Credit Memos" OUTER APPLY (SELECT
TOP (1) COUNT("No_ of Pstd_ Shipments$Sales Shipment Header"."No_") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Shipment Header$437dbf0e-0000..." AS "No_ of Pstd_
Shipments$Sales Shipment Header" WITH(READUNCOMMITTED) WHERE ("No_ of Pstd_ Shipments$Sales Shipment Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Pstd_ Shipments" OUTER
APPLY (SELECT TOP (1) COUNT("No_ of Pstd_ Invoices$Sales Invoice Header"."No_") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Invoice Header$437dbf0e-0000..." AS "No_ of
Pstd_ Invoices$Sales Invoice Header" WITH(READUNCOMMITTED) WHERE ("No_ of Pstd_ Invoices$Sales Invoice Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Pstd_ Invoices" OUTER
APPLY (SELECT TOP (1) COUNT("No_ of Pstd_ Return Receipts$Return Receipt Header"."No_") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Return Receipt Header$437dbf0e-0000..." AS
"No_ of Pstd_ Return Receipts$Return Receipt Header" WITH(READUNCOMMITTED) WHERE ("No_ of Pstd_ Return Receipts$Return Receipt Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Pstd_ Return Receipts" OUTER APPLY (SELECT TOP (1) COUNT("No_ of Pstd_ Credit Memos$Sales Cr_Memo Header"."No_") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$Sales Cr_Memo Header$437dbf0e-0000..." AS "No_ of Pstd_ Credit Memos$Sales Cr_Memo Header" WITH(READUNCOMMITTED) WHERE ("No_ of Pstd_ Credit Memos$Sales Cr_Memo Header"."Sell-to Customer No_"="Customer"."No_")) AS "SUB$No_ of Pstd_ Credit Memos" OUTER APPLY (SELECT TOP (1) COUNT("PFX Repayment Plans$PFX Repayment Plan"."No_") AS "$CNT" FROM "MyDatabase".dbo."MyCompany$PFX Repayment Plan$dcaf7477-0000..." AS "PFX Repayment Plans$PFX Repayment Plan" WITH(READUNCOMMITTED) WHERE ("PFX Repayment Plans$PFX Repayment Plan"."Customer No_"="Customer"."No_" AND "PFX Repayment Plans$PFX Repayment Plan"."Status"=@7)) AS "SUB$PFX Repayment Plans" OUTER APPLY (SELECT TOP (1) SUM("Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry"."SUM$Amount") AS "Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry$SUM$Amount" FROM "MyDatabase".dbo."MyCompany$Detailed Cust_ Ledg_ Entry$437dbf0e-0000...$VSIFT$Key13" AS "Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry" WITH(READUNCOMMITTED,NOEXPAND) WHERE ("Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry"."Initial Document Type"=@8 AND "Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry"."Entry Type"=@9 AND "Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry"."Customer No_"="Customer"."No_" AND "Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry"."Posting Date">=@10 AND "Finance Charge Memo Amounts$Detailed Cust_ Ledg_ Entry"."Posting Date"<=@11)) AS "SUB$Finance Charge Memo Amounts" WHERE ("Customer"."No_"=@0) AND ("Customer"."No_"<@12) ORDER BY "No_" DESC OPTION(FAST 50)The above query has the following Execution Plan 🤯
In this example the Customer table was extended by 7 APPs.
So, having only one companion table will reduce drastically the server load.
Why not a single table?
Reason 1: Microsoft wants the full control of the Base Application 😁 without any interference by 3rd party APPs.
Reason 2: some tenant in the world would break the 8k bytes limits of SQL Server rows. What about this limit?
According to the last SQL Server specifications, the maximum row size is 8.060 bytes. So, it seems that if you add 50 text fields with a length of 200 bytes each, you break this limit. Is it true? Hmm…
Rows can’t span pages; however, portions of the row may be moved off the row’s page, so the row can be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes. This doesn’t include the data stored in the text/image page type.
This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.
This is done whenever an insert or update operation increases the total size of the row beyond the 8,060-byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page.
Seeing is believing! 12k row made by nvarchars [SUCCESS]:
+8k row made by 510 decimal fields [FAIL]:
I did a quick survey on my Business Central customer base and nobody exceed these limits, also including nvarchar fields.
SELECT SUM(c.length) FROM sysobjects o, syscolumns c
WHERE (o.id = c.id) AND (o.xtype = 'U') AND
o.name IN (
'MyCompany$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972',
'MyCompany$Customer$43a5...',
'MyCompany$Customer$6be8...',
'MyCompany$Customer$8bc2...',
'MyCompany$Customer$c0db...',
'MyCompany$Customer$f092...',
'MyCompany$Customer$f728...'
)No multiple table indexes
Anyway with N tables o 2 tables you cannot create indexes that span across multiple tables. Maybe this is the most critical issue in large environments.
In Business Central 2021 release wave 1 and later, keys in table extension objects can include fields from the base table object and table extension object. However, a single key can’t include fields from both the base table object and table extension object. In other words, each key must contain fields from either the base table object or the table extension object
Hey: this is a SQL Server limit, not a Business Central limit. 😁
Conclusions
Reducing joins with partial loading with Read Committed Snapshot (yeah, another story…) will effectively increase the Business Central performances. Personally, I still do not understand the reason why not use a single table for all extensions, appending APP GUID at the end of each fields.
When the data model will change, in On Premises environments pay attention if you read directly from the database. All queries should be rewritten.
In the end, if you work with huge environments, also with the new release, you need to change development approach:
- using all Microsoft Stack (SQL Azure, Service Fabric, Service Bus…) to improve data ingestion
- reducing the number of extensions
- optimizing and optimizing and optimizing 😁 the code