DB Update node too slow

I also face a problem on DB Update.
For 110k rows, updating only values in 4 columns of a table with ~200 columns (the WHERE clause set on unique key.), It takes me like an 1 - 1.5 hour…

I am not sure how to make it faster…

Please see the recommendation here.

Hello,

@izaychik63 originally question was asked in topic you linked but as new database integration is now used as opposed to one discussed in topic I have removed question to new topic. (And seems batch size is not an issue here).

@cyrusl0822 is where column indexed? (Don’t know if it is automatically indexed in case of unique key, probably is…). Also are column types on updated columns exactly there same? Remember updating db column type int and KNIME type was double and it was running really slow until I figured it and changed to to Int in KNIME. And last one, does writing to database executed in expected duration?

Br,
Ivan

1 Like

Could you give us some more details. What kind of data base is it. How is it connected. How do other operations work. Is the key a number or a string?

Maybe you could post some screenshots from the configurations or even the workflow itself without giving away any secrets.

1 Like

What is the jdbc batch size set to? (with new DB nodes this should be OK by default, unless you changed it)

On top of that what columns are you updating? Is it number or short string or binary, possible “large” amounts of data? LOB columns (at least on oracle) are very slow.

1 Like

I’ve got just the same problem since I changed to the new KNIME-DB-Framework. I try to write apx. 65k linnes of data into an oracle db. My task with the old framework finished in a few seconds ( < 30 i guess). The same task with the new Framework, and of course, just the same JDBC-Driver (ojdbc8.jar) takes about 1 hour!

As “beginner” wrote “LOB columns are very slow” I took a special look to my columns. In deed, to reach my goal I want to write WKT-Geometries into a CLOB-Column in my oracle db. Just for testing, it’s not really a solution, I changes type mappings to write my WKT-Geometries (just strings) into a char-column. Right, I may loose data, because the file-type won’t be able to store all possible date incoming. Well, now it works fine - similar to the old KNIME-DB-Framework!

For short:
KNIME-DB (legacy): write with string -> char = fast
KNIME-DB (legacy): write with string -> clob = fast

KNIME-DB (actual): write with string -> char = fast
KNIME-DB (actual): write with string -> clob = very slow

I guess the default size (2G characters) of clob in oracle will be part of the problem, even if tested it with clob default field length in every setup above. Maybe KNIME will handle clob different in both Frameworks?

2 Likes

Hi Ingo,
the old database framework always used the PreparedStatement.setString() method whereas the new framework allows you to choose which mapping and thus which method should be used.
If you select String->VARCHAR in the Output Type Mapping tab, which is the default, the PreparedStatement.setString() method is used whereas if you select String->CLOB the PreparedStatement.setClob() method is used to insert the string into the database. The performance of these two methods depends on their implementation in the used JDBC driver.
Bye
Tobias

3 Likes

Given Tobias information I would check if setString() (eg String->char) truncates the data with your setup. Especially the Oracle version will matter.

In general setString should work up to 32767 chars. It’s also worth investigating how much characters (or bytes) you actually need. Since Oracle 12.1 varchar2 supports up to 32767 bytes (compared to previous 4000 byte limit and this change might need activation via settings).

So with oracle >=12.1 you might not even need clob column. Worth an analysis if you need more than 32767 bytes. A problem with clob is also they are terribly slow to read. (an option can be to ENABLE STORAGE IN ROW and increase the default value to 32767 bytes. So you get varchar2 speed up to 32767 bytes with benefit of possible larger storage. However changing this parameter means the table needs to be rebuilt from scratch.

1 Like

Hey Tobias, hey kienerj, thank a lot for your help. My OracleDB-Version is 19c an settings are to support up to 32k bytes in varchar2-Fields. But since now there is no option in the actual (new) DB-Framework for type mappings to varchar2. It doesn’t matter yet, because i can write my date (most times) into varchar, especially if the wkt-geometry is from type “point”.

Well, i hesitate to use varchar or varchar2 if clob in use with the legacy DB-Framwork work very well and performant?! There must be a difference in handling clob in comparison between the old and the new KNIME-DB-Framework.

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