tMSSqlRow - Is this a memory consuming Component?

[Anonymous]

Hi,

I have a simple job which uses tMSSqlRow to run a BIG query which involves cursors, reading data set and writing to table. 

Job works fine with small dataset but when I have a large dataset then I get Java heapspace error.

My assumption was this component should be using memory on SqlServer rather than on our Jobserver? Please correct me if I am wrong and also suggest any solutions. 

Thanks

[Anonymous]

Hi,

     The amount of memory consumed depends on the type of query or operation you are running within this component.

      The best way to validate it is a two prong approach. You will have to definitely validate the DB aspect of the process with DB tools. Apart from that, you will have to also verify the memory consumption and CPU consumption of the process. The memory consumption can be easily verified by using Memory run (link below).

https://help.talend.com/reader/C8mznD9TYsuB~SWG77PPDQ/Ydedlg7fsuyKkw9MMSWcVA

      I would also recommend to do an execution using TAC and monitor the Job server utilization metrics when you are running the flow. This approach will give you a clear idea about memory and CPU requirements for your process.

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

[Anonymous]

Thanks Nikhil, I will definitely try the memory run. But I know the query I am running is a huge one with lot of cursors operations and i m expecting it to run for quite long time. But my thinking was it would only eat up DB resources not Jobserver resources . Anyways is there any other comp alternative to tmssqlrow?

[Anonymous]

Hi,

     The reason could be because data might be pumping from this component to other. Since I have not seen your job flow and code base of MSSQLrow, I am assuming that could be the reason.

     You can do the processing in multiple ways. If you could elaborate the business use case happening within the cursor, we should be able to suggest the right Talend components.

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

[Anonymous]

Nikhil, 

Below is my job, it reads query from mysqlinput and passes it to row comp to process it. There is nothing after that so I am not expecting data to be passed out from component. The query itself has an insert script which inserts data to different tables which is done inside cursor.  

[Anonymous]

Hi,

   Could you please advise the input data volume coming from tMSSQLInput component?

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

[Anonymous]

Hi Nikhil,

Just one row is coming out from Input component and that is the Query that we have stored in DB and that query goes to MSSQLROW query tab as  + ((String)Queries.QueryText ) + " 

[Anonymous]

Hi,

    I would recommend to add the functionality of tMSSQLRow inside a Stored procedure in this case and verify the difference. When you are running from Stored Procedure, the full control go to the database and it will not transfer any interim data back to Talend area.

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

[Anonymous]

Hi Nikhil,

I converted the query to a stored proc and in my talend job I just put a tmssqlsp comp and called that stored procedure, it ran for an hour and again gave me a Java heap space error and failed. 

What do you think the issue might be in this case? job literally has two comp tfixedflow --> tmssqlsp  thats it.

if I run the query in stored proc directly it runs fine , I do not see any error on database level.

Thanks

Manju

[Anonymous]

@Manju

I used to face similar kind of issue for reading or loading large datasets.

What's is the configuration(RAM) of the Job Execution Server pointing to which are you are executing Talend Job? Hope it is at least 16 GB

Example if its 16 GB then set below parameters in
Run tab->Adv settings->JVM Settings

-Xms4G
-Xmx12G

Always keep at least 4 GB free out of total RAM for other services which are needed by OS to function properly.

Along with the above 2 parameters use the below parameter specifically for Java Heap Space Error

-XX:+UseConcMarkSweepGC

This should solve your problem.