I am using DB Reader Node, After the Row & Column filter nodes to fetch data from a Oracle database. I am facing a performance issue while using a DB Reader just for fetching 10000 rows.
I have updated the fetch size in Oracle Connector node from 10000 to 5000, I have updated the KNIME.ini file for memory usage to 8gb, which has increased a bit of performance, but not much.
Checked network and antivirus issue, both is fine.
At present to fetch the 10000 rows with 5 columns it is taking 30 mins.
Can anyone please support on this.
Ya @bruno29a I will update the flow, removing the row filter.
and adding the conditions in the SQL.
The table is not indexed, but yes it is pulling data from different tables in Oracle.
There is separate team working on Oracle DB, so a request for finetuning the table will take considerable time.
So I am trying to bring up a flow that shall pull the required fields.
@VishalM0228 if it is a view the main work will be done on the ORACLE server and KNIME itself can do little to speed things up since the data will have to be processed at hoc on the server. This is why it does not matter how many lines you choose since the VIEW will have to be processed anyway (especially if there are complicated joins with no indexes are involved …).
What you could take into consideration
set filters as early as possible to a Oracle code optimization might use them well in advance (‘report’ them to the stream, will depend on the database). If you only need a sub set of the data tell that right away
if it is a simple VIEW it might be an option to separately load data from the two underlying tables (with filters!) and then join them in KNIME. This of course very much depends on just how large the files are you want to load
test with single table and a larger amout of data to see if maybe your LAN connection is a bottle neck
Then you could think about trying to load the two tables in chunks (if there are suitable IDs or somthing) and store them locally and then join (again: how large is the data overall).
If you can or do not want to use CSV for this you could store the single loop results as individual parquet files in a folder and then read them back as one file - thus saving the column types.