The number has probably changed since that time as there were even some performance improvements for single-row inserts, but the performance gain can still be significant these days.Īs the multi-row insert may require an extra work in an application, one might tend to avoid it, but multi-row insert will pay off. If you open the performance topics redbook for DB2 8, you will find the statement saying that the multi-row operations can save up to 40% of CPU time compared to single-row insert. IBM introduced the multi-row inserts for DB2 for z/OS in version 8 in 2004. The reduction of the DB2 calls and traffic is the crucial difference between these two flavors of inserts. On the other hand, if you use multi-row insert instead, you are passing more data within one call and thus reducing the DB2 calls. If you are inserting many rows in a loop via a single-row insert, you call DB2 for every single row, which means you spend some time for the communication with the DB2. What’s the main difference between single-row and multi-row inserts? Simply the performance. Please note that there might be some other means that could be used in very specific case, like LOAD, clone tables, but let’s keep them away from this blog and focus on a single-row insert vs multi-row insert. or you can use multi-row insert facility.or you can INSERT via SELECT from other table or view.you can just issue many INSERT statements in your program (either static or dynamic).Multi-row insert on DB2 for z/OS with code examples Emil Kotrc, you ever needed to insert many rows into a DB2 table from your application? What are your options in such scenario? Depending on the nature of your data and application:
0 Comments
Leave a Reply. |