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
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
Highlighted
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