ChannelDB2

How long it takes to insert 1M rows into DB2?

Many times people ask me if DB2 will be fast enough for their application. They are usually most worried about INSERT performance. Often acceptable performance means ability to insert 1k rows per second. Can DB2 do that?

To get decent insert performance from DB2 you must turn autocommit off and use prepared statement. This is pretty simple to do and DB2 can insert that way about 5k rows per second into moderate sized (about 1GB) table. Its slightly faster (7k rows per sec) if you don't have any indexes on table defined because DB2 can skip index maintenance.

DB2 can be even better with multi row inserts. Syntax for multi row insert is INSERT INTO table(col,..) VALUES (row1,..), (row2,..).. This way faster than inserting just single data row per statement. Attached PHP script scrolltest.php is inserting 10 rows per insert statement and it is able to insert 1M rows in 30 seconds!

Views: 679

Tags: db2, insert, performance

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Try BLU Acceleration on Cloud

¬© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service