Campaign Classic V7 - Optimize SQL query generated in Database update wizard

Description

Background and context :
In most of the Campaign builds including Gold Standard 9032 ones, we 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.

Use case :

We have an existing recipient table or any other table which already has in excess of 1 million records.

We now need to add one extra column to that table which is of data type integer.

We add that column in the schema definition and we want to commit the changes in the database.

Challenge :

We encounter and issue while updating the database and 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

Solution :

When adding an integer column , we get the SQL statement generated like 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 no. of records it can take time.

On this page