I am quite new to KNIME Analytics Platform. Could I get a hint why my queries with a sql statement are taking so long?
I know the amount of data within the range of more than 2 years what I want is huge (in total aprox. 2.5 million rows with 10 columns [3 - 4 calculated]), but is there another way to get things faster ?
Is looping better than splitting the queries and concatenating them afterwards? What about writing the out to disc, is this faster than writing to memory?
depending on the tasks you want to achieve you could try do as much as possible directly on the database, e.g using the DB Joiner, DB Row Filter etc. This might already reduce the size of the table you need to fetch and will speed up the DB Reader / DB Query Reader node.
Keeping the data in memory is usually faster than writing to disk, so I’d keep it as it is.
Each Query Reader returns ~2.5 million rows? How long does it take to run one of them?
in general I would suggest to use a single DB Query Reader node. If the nodes are all connected to the same Oracle Connector the nodes will be executed one after the other since they all use the same connection.
If you want to parallelize the execution you need to add an Oracle Connector for each DB Query Reader which then would result in 4 independent connections to Oracle and thus parallel execution of the DB Query Reader nodes.
Also please check if the data fetching or the query execution takes most of the time. You can check what the node is currently doing (e.g. Executing query or fetching row x) when you hover with the mouse pointer on top of the traffic light.
If the query execution takes up most of the time you could consider adding an index to the DB table that includes the columns you are using to restrict the data. If the data fetching is slow you can try to increase or decrease the Fetch size in the Advanced tab of the Oracle Connector node which specifies how many rows should be sent per round trip from the DB server to KNIME.
thanks for your answers. I have chosen a combination of both of your answers.
In order to speed up the query, I have created a couple of predefined views directly on the database. In addition to that in KNIME I crawl the data in a parallel way. This works just fine and the time for waiting decreased a lot.
Thanks in advance!