Upgrading a large database is unnecessarily slow - Update inner join

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 5.1 EE SP3 (5.1.6), 5.2 EE SP1 (5.2.5)
• Fix Version/s: 5.1 EE SP4 (5.1.7), 5.2 EE SP2 (5.2.6)
• Component/s: Core Infrastructure > Upgrade Framework, Environments > Databases, Performance
• Labels:
  • 5.2_release_notes
• Environment: Oracle Database, SQL Server

Upgrading a large database is unnecessarily slow in SQL Server and Oracle databases.

MySQL supports a very developer friendly inner join for update statements. Oracle, SQL Server and other vendors support a more generic method of doing inner joins for an update using nested select statements. Currently, instead of using the more generic method, the upgrade process will iterate through every entry and call an update on that sql. For example, if there are 100,000 entries in the database, the upgrade process will make 1 sql select call and 100,000 updates. So to address this issue, the upgrade script will now always use the generic method of doing inner joins for an update.