jholste
asked on
Slow Append query - Access to Oracle with ODBC
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
Netminder
EE Admin
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.