32,000 inserts / bulk insert

[Bruce Whitehouse]

I've got an array of objects that has come back from an AS400 server.
There's approx 32000 objects in the array.

I need to insert these into an Oracle table. Obviously I can loop throug
the array and do 32,000 inserts, but it takes *so* long, and we'd run the
risk of suffering time outs.

Is there another way that anyone can suggest that I can do a bulk insert?
If anyone can point me in the direction of some code samples I'd be more
than grateful.

TIA, Bruce

 

[IbrahimMalluf]

If this is a one time transform, or if you have access to SQL-Server2k on
the same network, look into DTS.

Ibrahim

 

[William Ryan]

Use SQL Loader with Oracle, or SQL Servers DTS (you don't even need to have
SQL Server as a source or destination).

If it's only happening once, no big deal b/c you can let it run for a
night....but these things never just happen once. Using one of the server
side Data tools will yeild MUCH better results and are very reuseable....

HTH,

Bill

 

[Bruce Whitehouse]

Thanks guys

I was going to look into this, but unfortunately the process is run daily
(or more than once per day) - and the requirement is that it must be kicked
off by a click of a button within a .NET page.

So unless I look into doing some kinda integration with the SQL Loader, it
looks like I'll have to continue looping through, doing my 32,000 inserts.

Are there no methods within ADO.NET that would allow me to create the table,
with all its data and fire it off with one transaction to Oracle (or any
other DB)???

regards,
Bruce

 

[William \(Bill\) Vaughn]

I'm with the Mr. Ryan on this. DTS is a far more elegant (and safer)
solution--even if it has to run hourly.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Lyndon Hills]

Thanks guys

I was going to look into this, but unfortunately the process is run daily
(or more than once per day) - and the requirement is that it must be kicked
off by a click of a button within a .NET page.

So unless I look into doing some kinda integration with the SQL Loader, it
looks like I'll have to continue looping through, doing my 32,000 inserts.

Are there no methods within ADO.NET that would allow me to create the table,
with all its data and fire it off with one transaction to Oracle (or any
other DB)???

regards,
Bruce

As everybody + dog says use sql loader. However if you really must
do it this way then look into using Oracle's own .net library. It's
called ODP.NET, available from Oracle web site. It has a feature
called array binding, which I think will do what you want.

If the .NET page can be a forms app and not a web app you could have
that spawn a sql loader session......

Or how about an asp page that calls a web service that does the same
thing? Either ought to be easier, faster and more reliable, I'd think.

 

[Joe Fallon]

Even if you load the data into a datatable and call the .Update method it
still runs a single Update command for each row so it still sends 32,000
commands to the server.

You can code an ADO.Net transaction and wrap the whole loop in it if you
want.
If you parameterize the SQL statement (or use a Stoored Procedure) then you
might get some improved performance on the server side since it won;t
re-compile the query 32,000 times.

DTS is probably the best way to go though.

 

[Bruce Whitehouse]

DTS isn't an option.

We have Oracle, we don't have SQL Server.

 

[Bruce Whitehouse]

We don't have DTS, we can't use it

We're already using Oracle packages for the inserts, so that covers your
suggestion of using SP's.

regards,
Bruce

 

[William \(Bill\) Vaughn]

Perhaps because ADO.NET (in its present state) is a query interface, not a
maintenance/bulk load tool.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

So. Get A single license and use the DTS package and leave the server on the
CD.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________