Memory and Running Time issues while copying from Excel to SQL using Talend
I have a simple task of copying Excel data to SQL tables. I am executing one stored procedure initially to delete tables entries. Then I have Excel input from which I am copying data to the SQL tables using tMap.
I have 20 tables to copy data to. I have relatively small number of table entries (10-100) to copy. Still when I am executing my task, it takes a very long time (5-10 mins) and after copying 12 tables entries its running out of memory.
My work flow is.. (stored procedure ->(on subjob ok) -> excel input -> tmap -> tMSSqlOutput -> (on component ok) -> excel input -> tmap -> tMSSqlOutput (on component ok) - > ...... -> excel input -> tmap -> tMSSqlOutput)
My Excel sheet is on my local machine where as I am copying data to SQL tables on a server. I have kept my run/debug settings as Xms 1024M, Xmx 8192m. But still its not working.
May I know what can I do to solve this issue?
I am running my talend on a VM (Virtual Machine). I have attached the screenshot of my job.
You should be running all of these separate steps in separate subjobs, using "on subjob ok" to link them, so that the Java garbage collector can better reallocate memory between steps.
If this still doesn't work you could separate them into completely separate jobs and link them all using tRunJob components and make sure to select to tick "Use an independent process to run subjob":
This will spawn a completely new JVM instance for the process and thus not be memory tied by the JVM. That said, you should be careful not to spawn too many JVM instances as there will be some overhead in the start up of the JVM and obviously you are still limited by any physical memory constraints.
It belongs in a separate question really but you may also find some benefit to using parallelisation in your job to improve performance.
- Why 5.0 / 2 returns 2.5000000000000000 (scale of 16) instead of 2.5 (scale of 1)?
- SQL - How to SUM and use WHERE clause on this SUM using 4 tables?
- The best way of storing many to many hierarchical data in sql
- How to group and number SQL results?
- How to prevent PostgreSQL from altering my nicely formatted SQL definitions
- How to perform division in SQL Server?
- Table Primary Key and Foreign Key Relationship Design for Invoices with Summary and Detailed Sections
- How to put the select result (column) in an array in PL/SQL
- SQL Query to use a Case Statement within and Aggregate Function
- How to get date difference from a column?
- Prioritization of data in SQL
- Operand type clash: date is incompatible with smallint error in sql server
- Are GUIDs timely ordered ? If ORDER BY used with a GUID variable type, will records created lately come late?
- Find SQL Server Job Related to Existing SSIS Package in Visual Studio
- Normalize Google Sheet table (1st step)
- How to get column between two values as per user input
- Problems selecting multiple column
- SQL convert nvarchar to float
- MySQL Workbench edit data
- Combine overlapping date ranges
- How to use values from CTE in ON CONFLICT UPDATE
- Expand the date range between two dates
- pyspark statistical window function keeps calculating NULL values
- sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible'
- Sum a record with its previous not-null ordered by timestamp
- Prevent redundant results in SQL query
- SQL Query in Snowflake - Calculate Days of Supply
- How can I find out what FOREIGN KEY constraint references a table in SQL Server?
- How to extract column names from SQL query using Python
- Select on self-referencing table where all chains meet a condition