The article explains the reasons why we get WARN-3111 and how to handle them
APPLY SQL is long running
Applicable Error Code
WARN-3111: Apply SQL is running too long
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.
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