Highlighted

Bulk insert to Oracle

Advisor ,
Nov 30, 2018

Copy link to clipboard

Copied

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

Views

510

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Bulk insert to Oracle

Advisor ,
Nov 30, 2018

Copy link to clipboard

Copied

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

Views

511

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Nov 30, 2018 0
Adobe Community Professional ,
Nov 30, 2018

Copy link to clipboard

Copied

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 

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 30, 2018 0
Advisor ,
Nov 30, 2018

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 30, 2018 0
LEGEND ,
Nov 30, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 30, 2018 0
Adobe Community Professional ,
Nov 30, 2018

Copy link to clipboard

Copied

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).

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 30, 2018 0
Advisor ,
Nov 30, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 30, 2018 0
jfb00 LATEST
Advisor ,
Dec 03, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2018 0
LEGEND ,
Nov 30, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 30, 2018 0