Highlighted

Oracle multiple inserts performance issue

Advisor ,
Aug 10, 2015

Copy link to clipboard

Copied

Hi All,

We are creating a query using  <cfset QueryAddrow(qryMasterFile) />

At the time to insert the data to oracle db we do:

<cftransaction action = "begin">

  <cftry>

     <cfquery name="deleteMasterFile" datasource="#application.str_dsn#">

              DELETE FROM master_file

              WHERE my_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.my_id#">

     </cfquery>

    <cfoutput query = "qryMasterFile">

          <cfquery name = "insertData" datasource = "#application.str_dsn#">

                               INSERT INTO MASTER_FILE ( list of our fields)

                                VALUES (sq_my_id.nextVal, 

                                <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.req_id#" />,

                                <cfqueryparam cfsqltype="cf_sql_date" value = "#qryMasterFile.DATE#">, and all the rest of fields.

                                )

         </cfquery>

    </cfoutput>

        <cfcatch type = "any">

                        <cftransaction action = "rollback" />

                        <cfset result['success'] = false />

                        <cfset result['errors'].msg = "Error occured inserting data. #cfcatch.message# #cfcatch.detail#" />

        </cfcatch>

   </cftry>

</cftransaction>

As you see, we are going to the DB for every record. I was checking online and I found that I can do bulk inserts using commit but I want to be able to rollback if any error.

How can I improve the oracle insert performance of 100K rows?

Thanks in advanced!

Views

285

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

Oracle multiple inserts performance issue

Advisor ,
Aug 10, 2015

Copy link to clipboard

Copied

Hi All,

We are creating a query using  <cfset QueryAddrow(qryMasterFile) />

At the time to insert the data to oracle db we do:

<cftransaction action = "begin">

  <cftry>

     <cfquery name="deleteMasterFile" datasource="#application.str_dsn#">

              DELETE FROM master_file

              WHERE my_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.my_id#">

     </cfquery>

    <cfoutput query = "qryMasterFile">

          <cfquery name = "insertData" datasource = "#application.str_dsn#">

                               INSERT INTO MASTER_FILE ( list of our fields)

                                VALUES (sq_my_id.nextVal, 

                                <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.req_id#" />,

                                <cfqueryparam cfsqltype="cf_sql_date" value = "#qryMasterFile.DATE#">, and all the rest of fields.

                                )

         </cfquery>

    </cfoutput>

        <cfcatch type = "any">

                        <cftransaction action = "rollback" />

                        <cfset result['success'] = false />

                        <cfset result['errors'].msg = "Error occured inserting data. #cfcatch.message# #cfcatch.detail#" />

        </cfcatch>

   </cftry>

</cftransaction>

As you see, we are going to the DB for every record. I was checking online and I found that I can do bulk inserts using commit but I want to be able to rollback if any error.

How can I improve the oracle insert performance of 100K rows?

Thanks in advanced!

Views

286

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
Aug 10, 2015 0
haxtbh LATEST
Advocate ,
Aug 11, 2015

Copy link to clipboard

Copied

A lot of this can depends on your database, its not really a Coldfusion thing. Like you said, doing smaller inserts at a time will increase performance, but it also depends on what kind of indexes you have on the table, what kind of connection to the database etc.

You will still be able to use the commit option for cftransaction as this is a pending transaction. Rollback will just cancel the pending transaction.

You would be better asking a question like this somewhere that can help you on the database side.

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...
Aug 11, 2015 0