Hey all,
I have a tough question about one of my components that is not scaling well. I am running a query using the tPostgresqlInput component. The query, shown below, checks for circular references between an employee and the manager in a single table. For testing purposes, I have been using a 25,000 record dataset. When I run this query using PG Admin or DB Visualizer, it will take less than 2 seconds to run. Additionally, using SAP BODS (our current ETL tool) the query will also run in less than 2 seconds. However, when I run this query through tPostgresqlInput it will take over 5 minutes to run.
I have tried a few different things to speed this query up, but to no avail. Inside Talend, I have tried adding additional memory on the JVM and running the subjob in parallel. Comparing the run time of the query between Talend and at the database level proves that their is something hampering the performance when ran through Talend. Any help understanding why Talend struggles with this query would be greatly appreciated. We need to be able to scale this query to process up to 500,000 records.
Thank you for any help! I truly appreciate it!
Running Job In DB Visualizer: 0.003 seconds
Running Query in Talend: 346 seconds
Query:
WITH RECURSIVE circular_managers(unique_id, mgr_unique_id, depth, path, cycle) AS (
SELECT u.unique_id, u.mgr_unique_id, 1,
ARRAY[u.unique_id || '']::varchar[],
false
FROM table1 u
WHERE u.unique_id IS NOT NULL
UNION ALL
SELECT u.unique_id, u.mgr_unique_id, cm.depth + 1,
path || u.unique_id,
u.unique_id = ANY(path)
FROM table1 u, circular_managers cm
WHERE u.unique_id = cm.mgr_unique_id
AND u.unique_id IS NOT NULL
AND NOT cycle
)
SELECT
depth,
array_to_string(path, ' > ') circular_managers
FROM circular_managers
WHERE cycle
AND array_to_string(path, ' > ') NOT LIKE ' >%'
AND array_to_string(path, ' > ') NOT LIKE '% > > %'
AND path[1] = path[array_upper(path, 1)]
group by 1,2
