Hello, we have a fairly simple graph (attached) that reads data from a SQL Server DB, de-dupes the output, does a DB Join on another SQL Server DB, and updates or inserts records into the second DB depending on whether a row exists for the key values or not. The early phases of the graph (0-3) complete successfully (the query resultset from the initial DB_INPUT_TABLE is generally small for these phases), but for phase 4 the graph seems to cease processing after a short while (typically a couple of minutes). The Console shows the graph as still running, and no error is generated, but record counts for each component no longer increase. The number of records that this happens after varies greatly from run to run, but is generally in the tens of thousands. I have added a "and elp.curentrecno > xxxxxx" clause into the SQL for the DB_INPUT_TABLE, for which I amend the value of xxxxxx after each run based on how far through the processing it got. I have tried increasing Fetch size to 1000 but this does not appear to have made much difference.
The tables being read from are a reasonable size (17 million, 7 million and 132 million rows respectively) and the query normally completes within about 30 seconds - there are about 140,000 records still to process from the initial query for phase 4, but for future iterations we would obviously prefer to have the graph run through in one go.
Please can you advise what restrictions there might be on Clover handling this data, or any alternative methods for the processing? We are using Clover ETL Designer 2.9.7. Thanks, Matt.
The tables being read from are a reasonable size (17 million, 7 million and 132 million rows respectively) and the query normally completes within about 30 seconds - there are about 140,000 records still to process from the initial query for phase 4, but for future iterations we would obviously prefer to have the graph run through in one go.
Please can you advise what restrictions there might be on Clover handling this data, or any alternative methods for the processing? We are using Clover ETL Designer 2.9.7. Thanks, Matt.
-
Dear Matt,
I have taken a look at you graph and cannot see anything obviously wrong. Version 2.9.7 is unsupported for long time; so it is well possible that this issue was fixed. Because I do not have your database I cannot prove that. Maybe you can download trial version of 3.4.1 and check by yourself.
Also, you have 5 components in phase 4 - so it is not obvious which one hangs. I would recommend to change phases in a way that all components of current phase 4 will run in own subsequent phase. So DbInputTable in phase 4, Dedup in phase 5, DbJoin in phase 6, ... By this approach you will find problematic component. Or maybe problem will disappear. I would also take a look whether your database connection is set to be thread safe (second page of connection wizard).
Please let me know if you have further questions.
Please sign in to leave a comment.
Comments 2