Bulk insert to Oracle

[jfb00]

Hi All,

Trying to insert 200K records to Oracle 12c.

We did this concept couple years ago, it is taking 6min doing the inserts:

Re: pass query to oracle

Is there a better technique do to a faster bulk insert to Oracle with the new version of ColdFusion?

We are using CF11 but we can upgrade if it is necessary.

Thanks,

Johnny

[Charlie Arehart]

Johnny, you really need to know first where the hangup is, whether in cf or the db. Could be either or both  and the "fix" would depend on the actual problem.

So first, do you have anything monitoring cf and/or oracle? You really need one or both to get your best solution  Anything else would just be people guessing 

[jfb00]

Hi Charlie,

Thanks for your reply and help.

We are using timing code for every step. The 6mins is where the insert code (cfquery db connection) is taking in place.

How can I figured if it is the cf or db?

[WolfShade]

Just checked the link you provided.    But this method is still being slow/bottlenecked?

If that's the case, then Charlie is correct in that you need to get monitoring going on both CF and Oracle to find out what is slowing things down.

V/r,

^ _ ^

[jfb00]

Thanks for jumping here WolfShade.

Okay, so this logic should be the same in CF11.

The CF monitoring shows that it is doing the db connection. Let me research in how can I monitor Oracle.

Best,

Johnny

[jfb00]

Hi Guys,

The insert line by line is the issue in Oracle. I checked with Oracle and they say the best way is to use bulk insert as:

Bulk Inserts with Oracle

Now, I am researching in how to pass the cfquery data to oracle sp and I don't see any good db parameter type:

ColdFusion Help | cfqueryparam

Is there a way to construct some IN parameter and pass to oracle sp?

Thanks

[Charlie Arehart]

Ah, good. That was one way of "monitoring" cf that was an option  So now we know it IS just the insert (you say), and so it would seem it IS just the db (and not cf, though perhaps its somerhing in the cf db dsn definition that could have an impact).

Bur again now the attention should turn to monitoring oracle. I realize you may feel blind to it/not your job/not allowed to see monitoring of it. But someone should be able to help.

Once you know if (and better WHY) in oracle it's slow, then the solution there may become clear (and an oracle DBA should be able to help with this) 

Maybe the problem is that you have many indexes in the table/tables being inserted into. Most dbs offer an option to defer index writes (for the life of a task), to speed things up.

Or the DBA may recommend moving the insert out of cf and into oracle alone (as an so, perhaps), but I realize you may have your reason for preferring/needing to do it from cf.

Again  even a DBA is guessing at solutions until s/he has diagnostics in the db that tell you where the performance is hungup. There could be solutions in how the insert is done, how the db is configured, and even how the dbms is configured 

And finally. It may simply be that for some combination of reasons, it simply will take as long as it does (if tuning in the db is not possible or suited to doing for this one job).

[WolfShade]

In what format are the 200k records?  Query object?  Excel object?  JSON object?  CFDIRECTORY object (which is really just another query object)?

In Oracle, I believe the best way to insert multiple records, whether 100 records, 200k records, or one million records, is to do something like:

INSERT ALL
<!--- begin iterative looping --->
into TableA (col1, col2, col3, col4) VALUES (val1, val2, val3, val4)
<!--- end iterative looping --->
SELECT * FROM DUAL; <!--- THIS IS VERY IMPORTANT --->

HTH,

^ _ ^