DB2 Database reader performance

I need to read data from a db2 database to write files. I use the database reader node but i have poor performance with the node.
I have to read 65 millions rows, I try with only 1 million rows but the node make 2 minutes to execute. I have already change the fetch size (1 million rows) but nothing change.

I don’t understand why Knime with the same jdbc have poor performance between other tools.

Hi @dlab -

Welcome to the forum and sorry for the trouble. I’m not a database expert, but a fetch size of 1 million rows sounds a bit high to me. Have you tried something more along the lines of 50,000 to see if that helps?

Also, you may want to use the new DB nodes released in KNIME 4.0, if you can. They include substantial performance improvements relative to the older database nodes.

Hi,
is it possible that the other tools only show you a preview e.g. top 1000 row of the complete data set instead of fetching the whole data?
As Scott already mentioned you could try the new db nodes which also support streaming execution. This speeds-up the execution because subsequent nodes can work with the data as soon as the first rows arrive within KNIME.
Bye
Tobias

2 Likes

Hi,

I already try the new node. I try also 50,000 rows but nothing change.

I try the streaming execution but i can’t change for the database reader the job manager.

Hi,
you only need to set the Job Manager on the Component as you did and then move as much of the nodes that work with the DB data in KNIME into the Component to benefit most from the streaming execution. If you only move the DB Reader into the Component the execution won’t be faster since streaming only brings performance improvements if you chain several streamable nodes together.
Another thing that might decrease the speed is the main memory (Java heap space) available to KNIME. At a certain memory threshold KNIME starts to cache data tables to disc to free up more space but which costs performance. See the FAQ on how to increase your memory (Java heap space).
Bye
Tobias

Hello dlab,
we have identified some performance problems in the new type mapping framework that is used in the new database nodes which we address with the next bug fix release 4.0.2 which will be available soon. Once it is available please update your KNIME Analytics Platform and let us know if you still experience performance problems with the new db nodes.
Bye
Tobias

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.