Talend Job is taking more time to complete execution

Five Stars

Talend Job is taking more time to complete execution

Hi,

 

I am working on TOS. In this i have created a certain number of jobs and done schedule through 'windows task scheduler'.

 

My problem is, sometimes the job is getting completed within 2 mins and sometimes the same job is taking more time (approx 2 hrs) to complete the execution.

 

What is the reason for this and how could it be rectified?

Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,Need to analyze that ,when it took long time how much data was processing comparatively when it took 2 mins to complete and when it was executing longtime other tasks are running.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

I have analyzed that also, it seems to be same count 8 lakhs record.

For 8 lakhs records, sometimes it is getting completed within 2 mins and for the same amount of data it is taking more time.

Regarding execution time - the job got executed in morning when there is no updates happens in source.
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,Ok..how was the network speed when it took long time comparatively to 2 mins time?

and as mentioned when it took long time any other jobs are executing compare  to 2 mins run?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

--Network speed is same as normal run.
--Yeah, at the same time i have tried to run another job as well. It got completed within 2 mins for 5 lakhs record
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,when it was taking long time,is it takes time to fetch data?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

Yeah, it if fetching and processing the data at '12 rows/sec' or '5 rows/sec'
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,can you analyse source statistics when that job was running long time and less time?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: Talend Job is taking more time to complete execution

Hi, 

 

Q1) How many components you are having inside the talend job.

Q2) How many sub jobs are inside your main job

Q3) what are the sources, targets and lookups used and their types

 

Use TJava to print system time after each and every component. And share the log of execution  to analyze further.

Five Stars

Re: Talend Job is taking more time to complete execution

-- I have used only 3 components
-- There is no sub jobs inside my main job.
-- Source - SQl and target also SQL, no lookups have been used.
Six Stars

Re: Talend Job is taking more time to complete execution

make sure the tables you use are not being locked by other objects. if other user or other job has locked your table. writing to that table will be kept on hold till the lock is released. 

 

Also see how much throughput you are getting for the mapping. 

 

Use bullk load component to load to target

Five Stars

Re: Talend Job is taking more time to complete execution

--Do the tables got locked while fetching the data through talend?

--Bulk load is better option, but i am facing some error. 'Access denied'

 

"Exception in component tDBOutputBulkExec_1_tMBE (New_component)
java.sql.SQLException: Cannot bulk load because the file "C:/Users/*****/Desktop/talend/TOS_DI-20180411_1414-V7.0.1/workspace/mssql_data.txt" could not be opened. Operating system error code 5(Access is denied.).
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282)
at talend_local.new_component_0_1.New_component.tDBInput_3Process(New_component.java:1180)
at talend_local.new_component_0_1.New_component.runJobInTOS(New_component.java:1522)
at talend_local.new_component_0_1.New_component.main(New_component.java:1360)"

 

and also I have one more doubt.

 

--Do we enable bulk load permission in sql database?

Employee

Re: Talend Job is taking more time to complete execution

Hi,

 

    Did you check the DB statistics from your DBA to verify that DB is having enough resources?

 

    I am having doubt about the performance of DB, number of simultaneous connections allowed, usage of indexes etc. (to say a few).

 

    Also please check the memory and CPU utilization of the server where Talend jobs are running (you have already checked the network part).

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: Talend Job is taking more time to complete execution

--In db statistics, what i need to check?
--There is no limit for simultaneous connections. Even-though I am executing the table alone which is taking too much time, at that time also same problem occurs.
--CPU and memory usage is around 70%
Employee

Re: Talend Job is taking more time to complete execution

Hi,

 

   If its Oracle, I would have asked DBAs to provide AWR and ADDM reports.

 

https://oracle-base.com/articles/10g/automatic-database-diagnostic-monitor-10g

 

   I am not sure the equivalent of ADDM report in SQL Server but your DBA can help you in it. 

 

   Are you having enough CPU and memory in the machine where you are running Talend jobs? Please refer the recommended Resource size for enterprise products as a bench marks.

 

https://talendpnp.github.io/architecture-center/reference-architecture/summer-2018-7.0/products/comm...

 

   You need to have comparable infrastructure in your server like a job server.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: Talend Job is taking more time to complete execution

Do we need to enable any options to make bulk load in sql through talend?
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,to load data using Bulk method ,you should use combination of tOracleOutputBulk and tOracleOutputBulk

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

@manodwhb, i am using sql database not oracle.....so i have to use tmssqloutputbulk component only right..
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,if you are using MSSql server then yes.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

I have used tmssqloutputbulk component, but it is showing error at the end....

Error : You do not have permission to use bulk load statement
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,tmssqloutputbulk  will create a Bulk file to load into DB,so may be you may not have access to create BULK file ,please check the access to create a file in that location

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

Now i can able to create a file based on bulkload, but i have to load it into sql table.

At the time of loading the data with tmssqloutputbulkexecution component and i am getting the same error.

"You do not have permission to use bulk load statement". It seems that, do we need to enable any settings in sql database level?
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,yes in DB side you need to do I belive

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

@manodwhb, While using bulkload component for sql, i am facing one problem.

The data type boolean is automatically converting into "true,false" in the text file before loading to the output table. So the data can't be loaded in the target.
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,Yes for the Boolean values it should be true or false. what is your target DB type? based on your DB allows you need to manage that filed as string.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,if i understand correctly you need to convert true to 1 and false to 0 .

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

@manodwhb, yes...i need to convert true to 1 and false to 0. Because my target data type also boolean.
Forteen Stars

Re: Talend Job is taking more time to complete execution

@Rajkumar ,where you were creating true/false,you can generate 1/0 but the o/p column data type should be string.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Talend Job is taking more time to complete execution

@manodwhb, my understanding correct means, there is no data type called 'boolean' while making bulkload right?
Five Stars

Re: Talend Job is taking more time to complete execution

@manodwhb, but my need is boolean data type.....so do any other way to reproduce the same in text file also?

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

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

Download