Array Insert with ODP.NET

ODP.NET supports array insert in two ways:

1. Binding arrays using ArrayBindCount

cmd.Parameters.Add("firstname",
OracleDbType.Varchar2,
firstNames,
ParameterDirection.Input);

where firstNames is string[]

2. Binding to PL/SQL associative Arrays

OracleParameter pFirstname = new OracleParameter("firstname",
OracleDbType.Varchar2,
ParameterDirection.Input);
pFirstname.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstname.Value = firstNames;

where firstNames is string[]
and the stored procedures accepts firstname as TYPE id_type IS TABLE OF bulktesttable.id%TYPE INDEX BY PLS_INTEGER;


---

The syntax is well documented in the ODP.NET guide.
So far so good.

The interesting question is the performance difference. In order to get an idea about the difference, I wrote a small application which allows me to choose between
1. standard ADO.NET adapter insert
2. array insert using ArrayBindCount
3. array insert using PL/SQL associative Arrays



and changing the number of rows and the the rowsize to be inserted.

---

Of course, I expected a difference in performance. But array inserts are really faster. I measured up to 10 times better performance, depending on row size and number of rows.
Array inserts are best performing having a high number of small records. Using Stored Procedures offers sometimes a small performance gain, but it will be mostly useful if you have some more logic to be applied (such as generating primary keys).

If you would like to extend the tests for yourself, just ask for my code in order to get started.

Lesen Sie den vollständigen Eintrag unter http://ursmeier.blogspot.com/2007/11/array-insert-with-odpnet.html