Slow Append query - Access to Oracle with ODBC

jholste
jholste used Ask the Experts™
on
I am using an Access front end to allow users to append records to an Oracle table through ODBC.  

I am suprised by how slow the query runs -- it takes over 5 minutes append 2000 records.  I am not doing anything fancy in the query, it is a simple INSERT INTO (SELECT FROM).

Does anyone have any tips for speeding up an append query?  I have indexes on the same fields in each table, and that doesn't seemed to help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
There are a lot of factors that can cause this -

Inserts take much longer depending on:
-number of indexes on the table
-size
-triggers
-other programs accessing the db (types of locking used)

etc. etc.  This is not an easy problem to troubleshoot but I would look at the number of indexes you have first and use only the ones that help.
Commented:
The fastest way would be to create a passthrough query with a "full blown" INSERT statement I guess.

You would need to create a query of the passthrough type (create a new query but add no table and select from the Query menu the specials)
This will open a text window where you can enter a query in Oracle format.

Now you'll have to create code to fill that text like:

dim qd as querydef
dim rs as recordset

set qd = currentdb.QueryDefs("<passthrough query name>")

set rs = currentdb.openrecordset("SELECT FROM")

rs.movefirst
WHILE not rs.eof
  qd.SQL = "INSERT INTO X (field1, fiel2) VALUES ("&rs!field1&", "&rs!field2&")"
  currenntdb.execute ("<passthrough query name>")
  rs.movenext
wend

Getting the idea ?

Nic;o)





ODBC is slow.
Network traffic is a factor.
As stated above, Indexes on the destination table SLOW DOWN inserts.  They do not speed them up.
Plus there are Oracle things like the number and size of available rollback segments, current CPU utilization on whatever box the db is on (ie, what other processes are you fighting for resouces.)

Sounds to me like Nico's approach is your best bet.

Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: nico5038  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
Per recommendation, force-accepted.

Netminder
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial