Bulk updating multiple columns in oracle
To investigate the influence the number of columns has on performance, I made an additional set of tests for tables with seven, ten and 23 columns.I ran tests wrapping one, two, five, ten, 25, 50, and 100 rows in a single insert statement (abandoning the 5 row tests).Before implementing parallel loading we wanted to find a simple way to speed up the performance of our procedure, something small that made a big difference – multiple rows insert.Instead of calling a single row insert, I sent five rows in the one statement.I then created a procedure that built a loop that parsed the data and inserted it into the target table. We were going to split the staging data into two parts and process each part in parallel.We had enough server resources for two parallel processes, so we just needed to add two parameters in a procedure to assign the starting row and the ending row of the staging table, and implement parallel calls of procedures in the Informatica workflow.This file had 220,000 rows, each of which had 840 delimited values and it had to be turned into 70 million rows for a target table.
To compare data-load methods, I chose a common example of a client program loading data. An example of inserting multiple rows: Insert into t2(c1,c2) values(1,’A’),(2,’B’),(3,’C’); which is equivalent to insert into t2(c1,c2) select 1,’A’ union all select 2,’B’ union all select 3,’C’; which was valid in previous versions.
We engaged two parallel processes and it consumed about 60% of the server resources, so users could still load files of other types at the same time.
I was intrigued by the multiple rows insert method of loading data, and wondered whether I could have improved the performance by doing it a different way. The database and the client applications were placed on the same personal computer (Lenovo Think Centre M90, Windows XP).
To investigate the performance difference between the single row insert and multiple rows insert, I created a program which dynamically formed an statement, which loaded one million rows in 9 seconds.
In contrast, single row insert took 57 seconds to load one million rows.
Search for bulk updating multiple columns in oracle:
During every test, I loaded a million rows into a table.