I need to insert or update an MS SQL Server table with a lookup (the same table) of 2M rows (see screenshots), after 10,000 rows the job is "locked" and no more rows are read/inserted or updated. The only solution is to kill the job There are more than 2M rows to read from table "X_PROFIL" SQL Server activity monitor shows the transaction is in ASYNC_NETWORK_IO Wait Type I'm aware of this post http://www.talendforge.org/forum/viewtopic.php?id=13451 but I wanted to know if somebody else got this issue and how to resolve it on the Talend side.
Hi Have you solved the rowlock issue? Maybe you misunderstand what I mentioned. Sorry for confusing you. I mean to solve rowlock issue, you'd better not use tOracleOutputBulkExec component. Use tOracleOutput instead. Regards, Pedro
Hi Pedro, the following request (which is what I'm trying to do with Talend) took 5 minutes (fast enough I think) with both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT OFF (no ALTER DATABASE done)
use CRM update CRM_PROFIL set ID_SOURCE = imp_dol..X_PROFIL.ID_SOURCE, CD_SOURCE = imp_dol..X_PROFIL.CD_SOURCE, CD_MARQUE = imp_dol..X_PROFIL.CD_MARQUE, CD_CIVILITE = imp_dol..X_PROFIL.CD_CIVILITE, NOM = imp_dol..X_PROFIL.NOM, PRENOM = imp_dol..X_PROFIL.PRENOM, CD_SEXE_CIVILITE = imp_dol..X_PROFIL.CD_SEXE_CIVILITE, DT_NAISS = imp_dol..X_PROFIL.DT_NAISS, CD_PAYS = imp_dol..X_PROFIL.CD_PAYS, ID_REGION = imp_dol..X_PROFIL.ID_REGION, IS_DELETED = imp_dol..X_PROFIL.IS_DELETED, CD_ZONEGEO_FRANCE = imp_dol..X_PROFIL.CD_ZONEGEO_FRANCE, CD_ZONEGEO = imp_dol..X_PROFIL.CD_ZONEGEO from CRM_PROFIL,imp_dol..X_PROFIL where CRM_PROFIL.CD_SOURCE = 'DOL' AND CRM_PROFIL.ID_SOURCE=IMP_DOL..X_PROFIL.ID_SOURCE (2844734 row(s) affected) So I don't undestand what's wrong with Talend
select snapshot_isolation_state,is_read_committed_snapshot_on from sys.databases where name = 'CRM' snapshot_isolation_state = 0 is_read_commited_snapshot_on = 0
Hi Didier Since all solutions above I used can't solve this issue... I hope I can point out the root reason. But I can't. In fact, if you consider talend job as a Java application, you will find various topics online and various solutions. To fix it, you'd better trace the entire environment, SQL Server, Talend, network... Regards, Pedro