Optimize SQL query generated in database update wizard

This article addresses the issue of adding an integer field to an existing schema in Adobe Campaign, particularly when records already exist. To resolve this, optimize the SQL query by condensing the four commands (as discussed in this article) into one. This should ensure efficient operation without performance issues or deadlocks.

Description description

Environment

Issue/Symptoms

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:

Resolution resolution

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 i<column_name> SET NOT NULL;

The problem here is that there are four commands executed in the database in parallel, which can cause performance issues or a deadlock.
The solution is to condense these four commands into one and still make 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 also updates all  existing  records to 0, which can take time depending on the number of records.

Cause

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

If you now need to add one extra column to that table, which is of data type Integer. In that case, 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.