Passing an array to Oracle using .net/ Data Access / ADO.net

HI,

I was wondering if anyone can help me with the following problem:

I am using OracleClient class provided by Microsoft for my data access
layer. I have written all the object oriented code using OracleClient ( i am using DAAB provided by GotDotNet.ApplicationBlocks.Data for Oracle).

My database is Oracle 9i.
I am using .Net Framework 1.1 and it is not possible for me to update it to 2.0 right now.

I have a stored procedure that inserts a row in the Oracle database.

The problem is:

I may have to call that stored proceudre from 1-2000 times. My stored
procedure currently handles data for only one row at a time. But calling
stored procedure for each row is very slow, which i beleive is because
of the communication problem. for ex. for one row insert it is taking
about a sec but for 1000 rows it takes 38 minutes.

I was wondering is there any way to send all the possible rows in one
round trip

I know ODP.net accepts pl/sql associative array but that would mean i
would have to change the way my Data Access layer operates right now.
Can some body help me with this

Answer this question

Passing an array to Oracle using .net/ Data Access / ADO.net

HoffmanRon

If you were using 2.0, you could still use stored procedures. You would just set your InsertCommand to be the same as what you are currently using with your non-batched case, and when you set up the parameters, you would just bind them to a column in the DataTable.

However, I don't know of a good way to do this with 1.1 other than using ODP.NET, as you have already mentioned.

Thanks,

Sarah
Sandeep Chanda
I am using .Net framework 1.1 and I am using stored procedures as there is some logic that needs to be done on Oracle side. So your suggested solution will not work.

Thank you,
Amit Kejriwal
astan100

I do not know Oracle, but I believe it supports XML. You could form XML string with multiple records inside and pass it into stored procedure. See my article about how I did it with SQL Server 2000 from .NET 1.1

http://support.microsoft.com/kb/555266/en-us
Jabongga
If you really cannot use ODP.NET and really need to improve the perf, there are some other not so elegant things you can try to minimize the chatty conversation with the server:

* serialize your parameters into string / binary / xml and pass that as input into a stored proc which will then deserialize and use the data.

* Yet another solution is the poor man's batch. Essentially you create a command which is in practice a bunch of commands concatenated with multiple parameters.

Naturally, you'll need to measure how well these solutions perform in your specific scenario.

--VV [MS]
vascov@microsoft.com
ntsoo

If you use a DataAdapter, ADO.NET 2.0 has a new property called UpdateBatchSize.

See the following for more information:

Performing Batch Updates with a DataAdapter
http://msdn2.microsoft.com/en-us/library/kbbwt18a.aspx

OracleDataAdapter.UpdateBatchSize Property
http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter.updatebatchsize.aspx

Note that this is on the DataAdapter only, so you can't apply it directly to a command. Theoretically you could add your new rows into a DataTable and use an adapter with the InsertCommand set to the stored proc command. With a large amount of data to insert, this could be more overhead than you want, especially in terms of memory usage (also, I haven't tried to do this before, so there may be other pitfalls). Other than this, I don't know of any clean way to send batches of stored proc calls using System.Data.OracleClient.

Thanks,

Sarah
Passing an array to Oracle using .net/ Data Access / ADO.net

Answer this question