topic Re: High CPU usage with Access in Talend Studio

High CPU usage with Access

A5sys — Fri, 15 Nov 2024 23:42:18 GMT

Hello,

I am working with Talend Open Studio for Data Integration 7.3.1.

I need to copy some data from an Access Database to a MySQL Database. I am facing an issue with the CPU usage.

When the job is running, 100% of the CPU is used.

I think the problem is with access. I developped the same job with a file instead of the Access table and the CPU usage is correct (10% maximum).

I tried to increase the java memory but it didn't help.

Has anyone encoutered the same issue with Access Database ?

Best regards,

Adeline

Re: High CPU usage with Access

Anonymous — Fri, 24 Sep 2021 03:10:08 GMT

Is the Access Database located in local machine or a network shared folder? How many records? From the screenshot, I see there are two components that reading data from Access Database, for debugging, I would suggest to test each step to confirm the issue occur on which component/table? connectivity issue or due to big amount of data？

Regards

Shong

Re: High CPU usage with Access

ypiel — Fri, 24 Sep 2021 15:42:08 GMT

Hi,

Could you give us information on access connectors you use ? On the screen shoot we can identify them.
Are all input connectors connected to Access database and only the last output "ABONNEMENT" that is Mysql ?
how is accessed MS Access db ? file access or network connection ?
Can you reproduce the issue with a simple job that you could join to this ticket ?
Did you tried to deactivate all subjob (blue rectangle) and activate again on by one, top to bottom, to find where is the bottleneck ?
What is the size of the lookup ?
What is the link between 'tSetenv' next subjob ? The good way to synchronized subjobs is to use "On subjob OK" between first connector of each two subjob, in your case 'sys_synchro_talend'---- on subjob ok -----> Pink database "ABONNEMENT"
- https://help.talend.com/r/en-US/7.3/studio-user-guide-data-integration/trigger-connections-for-job

Thanks for answers,

Regards.

Re: High CPU usage with Access

A5sys — Mon, 27 Sep 2021 08:34:37 GMT

Hello,

Thanks for your answers.

I use tAccessInput with a filter in the query. The Access database is a file on my computer, i don't use a network connection.

In this job, the table "INSTITUT" in the Prejob and the table "ABONNEMENT" with the link "LECTURE" are Access tables. The table "sys_synchro_talend" and the others "ABONNEMENT" tables are MySQL tables.

I tried to deactivate all subjob and activate again one by one but the issue appeared with the first Access component (table "INSTITUT").

So I tried without the table "INSTITUT" in the Prejob. I had no issue with the first subjob or the loading of the lookup "ABONNEMENT", but when the job was connecting to Access table "ABONNEMENT", the CPU usage increased.

For this job, the tables "ABONNEMENT" only have 27 rows (in both database, MySQL and Access).

The link between the component "tSetEnv_2" and the next subjob is a "Run if" link. The next subjob is executed only if the output "CtrlDerSync.Controle"<=0.

I exported the job without the connection information and I joined it to this ticket.

Best regards,

Adeline

Re: High CPU usage with Access

ypiel — Wed, 29 Sep 2021 09:28:13 GMT

Thanks.

Firs of all I would advise you to declare all db connection in preJob and then use them in all db connectors. If not, "ABONNEMENT" access & mysql connectors will reconnect at each row.

Re: High CPU usage with Access

A5sys — Thu, 30 Sep 2021 15:12:10 GMT

Hello,

I used the db connection components. The execution is a little faster but i still have the CPU usage issue when Talend is working with Access.

Without the Access connection component, the Access Inputs execution were slow. Now, they are faster but the Access connection component is not (almost 20 seconds). With the MySQL connection component, it's immediate.

For information, I can't see the comments you made in your project because the project linked doesn't have your modifications.

Best regards,

Adeline

Re: High CPU usage with Access

ypiel — Fri, 01 Oct 2021 14:06:41 GMT

Is it possible for you to reproduce the issue with a simple job ?

[tDBInput(Access)] ---> [tLogRow] ? Try to complexify it until you reproduce.

On the other hand, how many rows is retrieved by the tMap lookup "ABONNEMENT (tDBInput2)(Mysql)" ? Maybe it is the tMap search that increase cpu usage.

Do you really need "All Matches" option for lookup ? Maybe 'First Match' could be ok ?

Regards.