Move the stored procedure logic to Talend having Loop with multiple SQL lines
Team,
We have attached stored procedure which need to migrate in Talend. I was wondering how can I loop through mulitple line of SQL codes with subquery and group by and sum operation. So, basically it's summary table population with Loop through calcuation based on Year, Period on YTD.
Please suggest the approach to convert this in Talend.
While it is obviously possible in Talend, why don't you just call the STored proc from Talend using tDBRow component?
I was thinking why to spend time to reinvent the wheel since you already have full logic in Stored procedure itself.
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
@nthampiThanks for the response. We are actually migrating the Stored procedure in Talend and also we need to improve the performace so, I think calling directly in Talend is going to provide same processing speed as before however, if we convert in Talend logic then it might help.
You can definitely rewrite the entire logic in Talend. But I am thinking in different direction also. If you have to rewrite the entire query and do all the aggregation functions in Talend, it means that you will have to provide the entire data from underlying tables to Talend. Right now, you must be getting the aggregated records only to next stage.
If the base tables have lot of records (say in 100,000s of rows), it means you will need more memory in Talend to store and process all those records and there will be extra network latency and resource usage to transfer those many records between DB server and Talend job server.
May be you can do each stage of aggregation queries directly within DB and then pick the result sets to Talend. That way, you will have best of both worlds. You can get rid of stored procedure and at the same time, utilize the DB computation power for aggregation. Once the aggregated data from different steps of stored procedure are ready, then you can join them using tMap and load to target tables.
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
