I'm working on enhancing a data pull from a terrabyte sized Oracle database for use within an MSAccess front end. The current plan is to append and update tables within Oracle using ODBC pass through queries or ADO connection objects, then copy the resulting much smaller data set to Access for further processing.
Oracle client: 11g
Access DB: 2010
Tnsnames.ora in place
Connection via ADO connection, or DAO/ODBC pass thru queries
i have also tried:
there are two issues i'm facing:
1) comparing performance with the SQL developer, where for some runs, Access is significantly slower, other times its fine
2) trapping errors, where it appears that all i get is the query timeout error, rather than a more informative error, such as a key violation.
in the first instance, creating about 6000 rows in the Oracle table takes about a 2 seconds using the SQL developer, and sometimes about 6 minutes with either ADO or DAO methods of queriing, but then sometimes its nearly as quick (?). Is there any way to figure out how to make the performance equivalent or consistent? This is probably not the forum, but maybe someone could post a link to where people are doing this more often (my google searches are returning spotty results)
But in addition, it seems like if there is an error in the query, such as a key violation, the query will wait all the way until the timeout value in many cases before returning just the timeout error, which tells me nothing. I need to keep that value pretty high, as sometimes the client will pull a lot more than 6000 records. Its also inconsistent, sometimes i get the key violation in 5-6 minutes, other times its all the way to 10 minutes before the timeout error happens, rather than almost immediatly with SQLdeveloper. is there any way to return error messages more quickly?
thanks much for the help - I'm going round in circles here.