DB Nodes very slow insert records to greenplum DB

I’m traing to insert 10000 records in to Greenplum DB and “DB insert”, “DB write” are work extremly slow, about 1 record per 10 seconds or slowly. The batch size parameter have no affected on speed of inserting.
Anyone have idea how to fix this?

Hi there,

welcome to KNIME Community Forum!

Which KNIME version are you running? Have you tried any other table? If not do that to check if it is connected to this particular table (and maybe to some weird type mapping) or to something else. For example try to write 1 column and 100 rows to a dummy table…

Br,
Ivan

Knime version - 4.0.2
Inserting 1 column no affect on speed
Insert to new table no affecting on speed
Inserting to other DB type work normal

1 Like

Hi @Mike_2323 -

I ran this by one of our database support folks. Is your data columnar by chance? If so, that might the issue - Greenplum can store data many different ways.

Have you tried our DB Loader node instead, which includes bulk loading functionality, and supports streaming?

2 Likes

Hi Scott,

I have the same issue with a Redshift database. The DB Loader does not support redshift at the moment and the “DB Writer” and “DB Insert” create insert statements row by row, which makes it extremely slow. Any suggestion?

I asked one of our solutions engineers, and he pointed out that one route recommended by Amazon is to copy data from S3 using the copy command. This will load the files from S3, and the files can be in Parquet, ORC, or csv. He recommends generating parquet files, and then uploading them to S3, then using the SQL Execute node to load the data.

More here: https://docs.aws.amazon.com/redshift/latest/dg/t_loading-tables-from-s3.html

2 Likes

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