Updating data using tDBOutput Component is taking longer time
Hi Team
I am trying to update a MySQL table and for that purpose I am using the tDBOutput Component.
I am having an input Excel file where the data is stored. It just contains two columns, the primary key column and the flag column which needs to be set.
I am connecting tFileInputExcel to tDBOutput component and in Action on Data, I am selecting as "Update". But the job is taking more than 1 hour just to update 500 records.
Please let me know what can be done to improve the performance.
The Job is simple, but the performance is unacceptable. Where is the Mysql database installed? Local or remote server? If it is the latter, it may be related to your network bandwidth. You can try the following test: 1 How about performance if you read data from MYSQL 2 Export the job script and move it to the machine where the Mysql server is located for execution to verify whether you have good performance.
First, check if you have activated the batch mode in the advanced settings of the component. Also please check if the key in the schema fits to a index in the table you want to update.
I would also check with an external tool like DBeaver the execution plan of the update command and optimize an index if possible.
