Optimize SQL query generated in database update wizard

Description description

Environment
Adobe Campaign
Adobe Campaign Classic

Issue
In most of the Adobe Campaign builds including the Gold Standard 9032 ones, you might encounter multiple issues when adding new columns to an existing schema.

The scope of this article is to address one specific problem which is related to:

  • Adding an integer field to an existing schema when records already exists.
  • Optimize the SQL query generated by the application to an efficient one for Postgres SQL.

Cause

This can happen when you, for example, have an existing recipient table or any other table which already has in excess of 1 million records.

If you now need to add one extra column to that table which is of data type Integer.

Then you add that column in the schema definition and want to commit the changes in the database.

While updating the database, the database encounters a deadlock or the query blocks other operations.

This is mainly due to updating of all records to 0 based on the SQL command generated by the database wizard.

Resolution resolution

Solution
When adding an integer column, you get the SQL statement generated similar to this:

ALTER TABLE NmsRecipient ADD column_name SMALLINT;
UPDATE NmsRecipient SET column_name = 0;
ALTER TABLE NmsRecipient ALTER COLUMN column_name SET Default 0;
ALTER TABLE NmsRecipient ALTER COLUMN icolumn_name SET NOT NULL;

The problem here is that there are 4 commands executed in the database in parallel which can cause performance issues or a deadlock.

The solution is to condense these 4 commands into one and still do the same changes as below:

ALTER TABLE NmsRecipient ADD iFichierLiveDataCliqueurs SMALLINT NOT NULL SET Default 0 ;

It does all the changes required, adding the column, setting the default, set NOT NULL, and also setting the value of existing records to 0 because of the NOT NULL constraint.

NOTE:

If there are multiple integer columns that need to be added, add them one by one and run the database wizard for each change.

This is to ensure smooth operation since the statement updates all  existing  records to 0 as well and depending on the number of records, it can take time.

recommendation-more-help
3d58f420-19b5-47a0-a122-5c9dab55ec7f