CODEWITHSUNDEEP
oracle-databasevbaadodb
Harsha Reddy
Harsha Reddy

Reputation: 99

Is there any way to improve performance while inserting rows into Oracle DB?

I need to insert more than thousand rows into an Oracle Database table every 6 hours using VBA module. During every run some complex forecasting calculation is done in VBA and the resulting values have to be inserted into the database.

The typical output Range consists of more than 1000 rows and 13 columns.

Currently I insert row by row and moreover an auto-commit runs after each insert(ADO DB default). Is there a better method or process for improving performance here?

It currently takes around 20 - 40 minutes depending on the server load. Any performance improvements Suggestions.

Upvotes: 2

Views: 1702

Answers (3)

Ole Henrik Skogstrøm
Ole Henrik Skogstrøm

Reputation: 6493

I had this problem my self. My problem was that i was opening and closing connections for each insert. This meant using 15 minutes to import about 1000 rows.

I solved this by keeping the connection open and sending new execute statements to the DB with a loop before closing the connection again.

This is my VBA:

' Setup the connection to the DB
Set conn = New ADODB.Connection
strConnection = "Provider=OraOLEDB.oracle;Data Source=connectionstring;User Id=username;Password=yourpasswrod;"

' Open DB connection
conn.ConnectionString = strConnection
conn.Open

Do Until IsEmpty(ActiveCell)
    conn.Execute ("insert into my_table ( user, user_info) values ('1', '" & ActiveCell.value &"')")
Loop

conn.Close
Set conn = Nothing

This redused the time to inport down to about 30 seconds. :)

Upvotes: 0

cagcowboy
cagcowboy

Reputation: 30848

May not be acceptable to you, but dropping any indexes on the table and then recreating once done would help. (Saves the overhead of having to update the index on each INSERT)

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81950

  1. Don't do a commit after every insert (if this is compatible with your requirements)

  2. Consider if using the append hint helps

  3. Don't know VBA (anymore) but look for batch inserts, i.e. something that inserts multiple statements in one go instead of every insert statement on its own

  4. Use prepare statements

  5. Consider disabling constraints during insert

Upvotes: 3

Related Questions