Database Nodes


#1

Hi all,

have couple of questions/comments regarding Database nodes.

  • Is there a way to see SQL code behind Database Update/Writer nodes?

  • With Database Update Node you can’t update Column based on its own value. Is there some other node capable of doing it or other way?

  • Font size for SQL Statement in Database SQL Executor Node is really small. At least on my screen :slight_smile:

  • Suggestion: Node that would perform Insert and Update together - if interested I can elaborate a bit more :wink:

Br,
Ivan


#2

Hi there!

Any news about this? Database Update node works really slowly…

Br,
Ivan


#3

For the Update / writer nodes, increase the JDBC batch size in the nodes Advanced config to 100. (IMHO this should be set as default as no one will every use a setting of 1 in a real-world application because it’s so slow.


#4

Hi!

I have been increased to 1000 already. Maybe a smaller number should help…

100 as a default seems ok.

Br,
Ivan


#5

Hi Ivan,
if you enable the JDBC logger via the Advanced tab of the DB Connector node you can find all SQL statements that are send to the database including the insert/update statements in the KNIME log. The nodes them self do not output the SQL statement.

Regarding the second question, why do you want to update a column with its own value?

I guess you have a high resolution display. Currently the font size of the SQL editor is fixed but I will create a feature request to make it adaptable. I guess you will have the same problem with other nodes that have an editor such as the R/Python nodes, Java Snippet or String Manipulation node.

The new db framework comes with the new DB Merge node which you are looking for. If the db provides a special command for this the operation is performed with a single statement e.g. the merge statement in Oracle.

Bye
Tobias


#6

Hi Ivan,
which database you are working with and experience slow performance for the update? Most of the time the update is very slow if you use columns in the where clause of the update that are not indexed. Creating an index for these columns in the database usually increase the update performance dramatically.
Bye
Tobias


#7

Hi Tobias!

I’m using PostgreSQL Connector and not DB Connector but I guess there is no difference so I can switch and try it.

I’m not trying to update it with its own value but based on its value. I’m trying something like this:
UPDATE table SET column1 = value1 WHERE column1 =value2;

Don’t have problem with Java snippet or String Manipulation node. Here is a picture:

Great news for DB Merge node :slight_smile:

Regarding your second post I’m using PostgreSQL (and Oracle but less often). I will try with indexes and see what happens.

Should batch size in database nodes be optimized based on configuration? (RAM, cells in memory)

Tnx a lot!

Br,
Ivan


#8

Have you also tried the new DB SQL Executor node which is part of the new DB framework? I would be interested to know if this editor also has the problem with the small font size.

The new DB Writer/Delete/Update/Merge node also come with a default batch size of 100. Optimizing the default batch size based on the available memory or number of cells in memory could be complex since a single cell could already contain a lot of data if it contains a complete document that should be written into a BLOB column in the database. That is why for now we have decided on a fixed batch size of 100 which should be ok for most cases.
Tobias


#9

Hi Tobias!

I just tried old and new SQL Executors on another computer and the font size is fine. So no more questions about it from my side.

Is the new batch size of 100 default and fixed or only default?

Thank you!

Br,
Ivan


#10

Hi Ivan,
the new batch size is only default and can be changed in the node dialog of the db manipulation node e.g. writer, update, delete and merge.
Bye
Tobias


#11

Ok. Tnx.
Ivan