How we can speed up data query from DB(Oracle) node Database Reader(Legacy)

[cppong]

Hi everyone,
I got some issues with the speed to query data from Oracle DB.
I use Database Reader (Legacy) to query data from a database. It is very very slow…you can see the pics I attached (or it was about Caching row setting?) …So, can we have the way to speed it up or any way to do? Please help.

[ipazin]

Hello @cppong,

have you tried the new DB Framework? It even has dedicated Oracle Connector node.

Br,
Ivan

[victor_palacios]

Could you use the non-legacy versions? Deprecated or Legacy nodes tend to be slower, but it’s a case-by-case basis.

[mlauber71]

@cppong you could try and read the data in chunks like in this example:

Download data from a database in chunks and append them to an CSV file –...

Export huge MS sql table to csv without fetching all records together

have you thought about doing it in chunks? And then appending the result to a CSV file. The example attached uses ROWIDs from SQLite. You could see if you either have a ROWIDs present or you could create one yourself. You can choose the size of the chunk/batch yourself (I use integers you might have to see if with very large numbers you might need big integers or something. Even a workaround with using strings might be possible). Only other idea would be to write a CSV file directly on a Big Da…

[cppong]

Hi Victor @victor_palacios
Thanks for the advice.

As I checked, I didn’t see the non-legacy for the Database Connector node. So, how I can get that node? See my captured pics.

I use KNIME version 4.2.0
Thanks again.

[cppong]

Never try this before. Thanks a lot @ipazin

[cppong]

Thanks a lot @mlauber71 for an example of the script.

[Thyme]

KNIME has gotten an overhaul of the DB framework with version 4.1. The nodes also got new names, they’re now called “DB something”:

You should update to the newest version your company allows you to. There’re new features, more speed and, most importantly: easier support on the forum

Also, as a general rule, versions x.y.0 should not be used for production purposes. Those version focus on new features and thus have the highest risk of introducing undetected bugs. source:

Problem when upgrading to 4.14.0

Hi Sébastien, please note that we generally discourage the use of .0 releases in production environments. Likely the issue is that a small error during the update process is preventing the server from properly starting. For example not deleting the old /webapps/knime folder, or not renaming the new .war file to knime.war. If you’d like, you can share the most recent catalina and localhost log files with support@knime.com. Maybe that already enables us to find the cause. Otherwise, we can als…

[cppong]

Thanks so much @Thyme I had checked it and found the database name will be DB… as you shown in the pics.

[cppong]

@ipazin Bingo !! I found the new DB version (instead use Legacy) & implemented all the DB connectors & SQL reader. It’s very fast to query. My problem was solved now. Thanks again and thanks to others for the great help!

[cppong]

Thanks for all of your help. It solved now ^^