CODEWITHSUNDEEP
sqlnetezza
user1384831
user1384831

Reputation: 219

Netezza - insert multiple rows is slow

How do I insert multiple rows at the same time in Netezza? I have about 20k records to insert but it takes 1 hr (!) to put them all in using single inserts 

INSERT INTO MYTABLE VALUES (2, 2, 2, 'E') , (3, 4, 5, 'Z') --doesn't work

This is what I'm using right now

INSERT INTO MYTABLE VALUES (2, 2, 2, 'E'); 
INSERT INTO MYTABLE VALUES (3, 4, 5, 'Z');
...

Upvotes: 2

Views: 8000

Answers (3)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10083

Netezza Performance Server (NPS) is mainly focuses on Online Analytic Transactions (OLAP). It mainly concerns with Bulk loading. So if your insertion records are huge, go for NZLOAD which will be very fast. Syntax: nzload -db -host -u -pw -t -df -delim -dateDelim -lf -bf

Upvotes: 2

ETLer
ETLer

Reputation: 81

Netezza does not perform well with singleton updates or inserts. What we've done is Upserts or what we call Kill and Fills. Using Cognos Data Manager (ETL) we find all the updates and then delete them from the table before we use NZ Load to append them back. For inserts, we just append them. Both using NZ Load. Works much better than the slow and painful single (one at a time) updates or inserts.

Upvotes: 8

Taryn
Taryn

Reputation: 247870

Not every database supports multiple row inserts. So you will have to use separate inserts or you can also use:

INSERT INTO MYTABLE
select 2, 2, 2, 'E' union all
select 3, 4, 5, 'Z'

Upvotes: 2

Related Questions