WARN-3111: Apply SQL is running too long

The article explains the reasons why we get WARN-3111 and how to handle them

Problem Details

Problem Statement

APPLY SQL is long running

Applicable Error Code

WARN-3111: Apply SQL is running too long

Affected Versions

Generic

Affected Platforms

Generic

Solution

This warning can be seen in the APPLY log when APPLY is taking a long time to execute DMLs on the target. It usually happens when an UPDATE or DELETE is executed on a large source table affecting millions of rows. The solution is to make sure that the target table has indexes to improve performance.

Updating 2 million rows on the source would not update the same records on the target instantly. Apply updates one record at a time, comparing the old and new values of columns. Updating 2 million rows on source with a single update creates and executes 2 million update statements on target.

Example

Let's consider the following dataset:

ID NAME CITY SALARY
111 Ricky Auckland 9000
112 Alex Sydney 8000
113 Glen Auckland 8000
114 Mathew Auckalnd 7500

Executing the following statement:

update employee set salary=10000 where city=Auckland

will be replicated to the target as three update statements:

update employee set salary=10000 where city=Auckland and id=111 and name='Ricky' and salary='9000';

update employee set salary=10000 where city=Auckland and id=113 and name='Glen' and salary='8000';

update employee set salary=10000 where city=Auckland and id=114 and name='Mathew' and salary='7500';

Therefore, for large tables with millions of records, having an index would improve APPLY's performance as records can be uniquely identified quickly.

Krupesh Desai August 22, 2014 11:58