Apologies for the semi-repeat post however I have read through many posts (many on old versions) and applied what I have found to little effect. Scenario: 4 main data sources - 2 excel files, 2 tables on sql server Large workflow - Just over 1,000 nodes End dataset - 350k rows, 56 columns Total Time to process - >4 hours Longest node to run - 87 minutes (Joiner: Left outer join) Hardware: Windows 10 VM on Azure Intel® Xeon® Platinum 8272CL 32GB RAM Have experimented with the Knime.ini configuration and is currently set to use 18GB and 1m cellsinmemory KNIME has been updated to the latest release The data is all transactional sales data from 4 different systems which need to be transformed before being combined with additional joins from other smaller ‘dictionaries’ such as FX rates etc. The longest node to run is a left outer join with the left table being the sales data (350k rows), and the right table being all the UK postcode prefixes (3k rows, 3 columns). This is done in order to validate which prefixes in the sales data are valid (most of the data comes from 2 of the systems without built in validation, also need to add cities to all of the data). Additional long running nodes are all joins, again with the 350k left and anywhere from 300 to 18k on the right. These include joins from a pre-downloaded dataset from Companies House (only records that we are matching to) and SIC lookups from the codes from Companies House. My thoughts: Do I really not have enough computing power to handle these joins quickly? I never seem to fully max out on RAM usage. I could re-work my workflow to apply the joins to the subsets of data before they are concatenated, but is this that much more efficient if they are potentially running at the same time anyway? Is there a method more efficient than using Joiner for this? Truly appreciate any help. Many thanks, Charlie

@knightyboy welcome to the KNIME forum These things come to my mind: joins are costly so it makes sense to plan ahead KNIME seems to be reworking the join nodes: i cannot find the post right now maybe @ScottF can help then it makes sense to try and do one join/step at a time i tried to use the cache node before a join on both sides in order to bring the data together and avoid additional internal copying ( Execute failed: Cannot read file "knime_container_20200301_1905559478659870280.bin.snappy" - #19 by mlauber71 ) then you could try and force the join node to do everything in memory and see if this is getting any faster then if a workflow becomes very large it tends to use more RAM. It might help to split it into several workflows and maybe call them from a parent workflow (no guarantee there - and of course the effort to rework the whole thing) - you could try with you two largest join files to see use the garbage collector to free memory you mentioned Azure: is this some virtual server? I sometimes had the impression that virtual or shared drives over networks make for poor KNIME performance. Local SSD fare much better. If this is the case caching again might be an option you would have to see if caching is better to be done on disk or in memory. Memory might be faster - but there is always a price to pay then you could try and change the internal storage from zip to parquet (cf. Execute failed: Cannot read file "knime_container_20200301_1905559478659870280.bin.snappy" - #2 by mlauber71 ). Although you might be careful with that Then concerning your data make sure you do not join large numbers of missing (or dummy IDs or something) against each other. That would also take more time complex strings could also be a problem I have thought about but never tried to build a reference file with numbers to my string IDs. Then join the numbers to each file and then join by numbers (that might be more costly than just living with the longer time) but combined with other approaches it might be a measure of last resort And well if everything else fails. You could try and employ a larger system or a database. Or you could try and see if the SQL server could handle some joins. You asked for speed but one more experiment could be to use Hive and the local big data environment. In theory Hive should be able to at least finish the job. But that would be more of an issue if your job would not finish at all. Optimizing KNIME workflows for performance | KNIME KNIME provides performance extensions such as the KNIME Big Data Connectors for executing Hive queries on Hadoop, or the KNIME Extension for Apache Spark for training models on Hadoop using Apache Spark. But sometimes it doesn’t make sense to run... OK since we are at it here a historical collection of entries about performance and KNIME [image] Processing hundreds of millions of records KNIME Analytics Platform A few remarks since it is difficult to judge from the screenshots: if you have to process that many files and lines it could make sense to do it in chunks and have some method in place to log the current status and be able to restart at a certain point. Eg. write results to disk after each 50 out of 900 processed files or something. Because if you do not have a very powerful infrastructure, compartmentalization might be a thing for you also this saving of steps in-between might function as som… [image] Large data tables missing? KNIME Analytics Platform Will the large dataset be imported and the problems will occur later or do you experience problems also when loading data? If you have problems with import it might help to use the R library Readr instead of KNIME file reader. I know of no formal restriction for data in KNIME but it depends on how much power your system might bring. These points might be able to help you check out the links below with hints from KNIME and other information about performance with large datasets from CSV it mig… [image] Knime 3.6 Crash when dealing with massive data KNIME Analytics Platform you could try and increase the assigned memory in the knime.ini configuration file but with only 4 GB there are limits to that ( https://www.knime.com/blog/optimizing-knime-workflows-for-performance ). On other option is to set the “Memory Policy” of the (sorter) node to “Write tables to disc”. Obviously this increases the load on the hard drive but it might save on RAM. [image] Also you could try and close other workflows, restart KNIME an try to use the garbage collector. [image] [image] T… [image] KNIME Takes Long to Load? KNIME Analytics Platform You might want to check your virus scanner. On certain systems with quite aggressive virus scanners I experienced KNIME to be slow. Since it can contain several 1,000 single files an ‘agressive’ virus scanner might scan all of them every time they are accessed/opened. When we changed the settings KNIME started faster. Of course there is a trade off between security and speed. But modern virus scanners might be able to have a flexible configuration. But of course the number of AddOns and the ove… [image] Knime is slowing down KNIME Analytics Platform That is certainly odd. To the best of my knowledge, there haven’t been any recent major changes to KNIME that are known to have a degrading effect on performance. From my experience, the most dominating performance factor in KNIME is the materialization of intermediate data tables on disk. Have you noticed the performance of your hard disk degrading independently of KNIME? While we have some performance improvements for KNIME in the pipeline, here are some small adjustments you can try out: Y…

Should it be taking this long?

KNIME Analytics Platform

mlauber71 June 25, 2020, 5:29am 6

Here is a way to have garbage collection without additional installation

1 Like