MS SQL suspended queries + ASYNC_NETWORK_IO

One Star

MS SQL suspended queries + ASYNC_NETWORK_IO

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.

regards,
Didier
Highlighted
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Hi
ASYNC_NETWORK_IO just means that SQL Server is waiting for the client application to accept the results.
You might try to edit query with 'ROWLOCK'.
SELECT * FROM table WITH (ROWLOCK)
or
SELECT * FROM table WITH (ROWLOCK, NOLOCK)

Besides, if you drop tMSSQLOutputBulkExec, change tMSSQLOuput instead.
Regards,
Pedro
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Hi Pedro,
I can't figure out to use tMSSQLOutputBulkExec
do I need first :

tMSSqlImput2
|
|
tMSSqlInput1------tFileOutPutDelimited (/myFloder/file.csv)
and then :
tFileInputDelimited(/myFloder/file.csv)----------tMSSQLOutputBulkExec(/myFloder/file.csv)
???
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

I've tryied to cut the job in two parts :
one for the insert
second for the update
the insert ran fine but the update flow stops when Batch Size is reached Smiley Sad
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

in fact the rows are updated but the number of rows are not updated and it takes a lot of time Smiley Sad
so if you think tMSSQLOutputBulkExec can speed the job can you give me a sample job ?
Thanks
Didier
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

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
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Hi Pedro,
I'm using tOracleOutput and the row lock is still a problem Smiley Sad
On monday I planned to use a tMSSqlRow to perform the update ... The classic way
Stay tuned ...
Regards
Didier
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Hi Didier
Why don't you use tMSSQLRow to execute these statements?
tMSSQLRow at the beginning of this job
  ALTER DATABASE database
SET ALLOW_SNAPSHOT_ISOLATION OFF

tMSSQLRow at the end of this job
ALTER DATABASE database
SET READ_COMMITTED_SNAPSHOT ON

Does it work? Wait for your feedback.
Regards,
Pedro
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

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 Smiley Sad

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
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Hi
So what do you mean? It has been solved? Or the job still doesn't work?
Regards,
Pedro
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

I mean it works great from Microsoft SQL Server Management Studio with no change on database settings but it doesn't from Talend ...
I will send you my job, maybe it can helps ?

Regards,
Didier
One Star

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

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

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.