topic Re: MS SQL suspended queries + ASYNC_NETWORK_IO in Talend Studio

MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Fri, 11 May 2012 07:43:50 GMT

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 https://community.talend.com/t5/Design-and-Development/Talend-SQL-queries-got-suspended-by-SQL-Server/td-p/76998 but I wanted to know if somebody else got this issue and how to resolve it on the Talend side.

regards,
Didier

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Fri, 11 May 2012 09:28:02 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Fri, 11 May 2012 13:45:15 GMT

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)
???

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Fri, 11 May 2012 14:19:44 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Fri, 11 May 2012 15:52:25 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Sat, 12 May 2012 03:44:42 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Sun, 13 May 2012 15:43:38 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Mon, 14 May 2012 04:14:38 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Mon, 14 May 2012 09:17:02 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Mon, 14 May 2012 09:21:29 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Mon, 14 May 2012 09:31:55 GMT

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

Re: MS SQL suspended queries + ASYNC_NETWORK_IO

Anonymous — Mon, 14 May 2012 10:47:34 GMT

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