Date Scaffolding and Non-Equi Joins at DB Level

This might be a question to @takbb, have been using the “Join Custom Condition - indexed - takbb” component with good results over the past year after your recommendation in this thread.

I now have the opportunity to push the logic to the DB (H2) level. Wondering if I can get any pointers or things to watch out for. I imagine a straight SQL query should do the trick, but the component itself is a maze within, so I’m wondering if I’m missing something in the complexity of the challenge.

Hi @qdmt , glad to hear the component has been working for you.

Most of the internals of the component are there just to make it work generically “as a component” and if you were writing a single specific solution by hand, you wouldn’t need most of what is there. And yes it probably is rather difficult to follow. :wink:

The main thing I would start with as a pointer is the significant performance improvement that came about by using indexes in the database.

I’m happy to give guidance but do you have specific questions that I can help with?

Hi @qdmt, A thought occurred to me… if you are wanting to push the logic totally to H2, then I wonder if this demo workflow is of use.

It has a couple of other components I put together where the aim is to place copies of KNIME tables on an H2 database so that you can then quickly perform SQL queries on the data using all the standard KNIME DB nodes.

The components used here are (slightly) less complicated than the JOINER component you mentioned, because their purpose is to only place one table into the database, instead of two (plus all the joining code).

They still perform the indexing though so that SQL queries can execute faster.