Append data to Oracle - SLOW

[nashman]

Hi All,

I need some help in figuring out if there is a faster way of appending data to an Oracle BE table.

I have an Access database that opens a recordset and reads a field. The VBA code then needs to convert the field data ( which is a series of ASCII characters ), convert this ASCII character to its ASCII value number ( ie: the period "." is ASCII code 46 ), it then looks to the each and every ASCII character in the field, counts the number of instances for each ASCII character, then appends the values for the ASCII code and the count to a table.

The problem is that as it works its way through the loop, it appends to an Access table very quickly. However, I would like to place this data on one of our Oracle databases for a number of users to be able to access the data. Keep in mind that I cannot change the field with the ASCII series, so can only convert it for a relational database table. When I try to run the loop to append to an Oracle table, the process is so slow that it is impossible to use this method.

I think this may be related to Oracle trying to create some kind of "commit" and "back out" structure and is slowing the process. The Oracle database is hosted so I can't ask for changes there either.

Any help will be be kindly appreciated.

Thanks

 

[TheFitz]

I have had this problem and I spoke to one of our Oracle DBA's and he told me that basically the speed of it is due to the commit every record and the amount of data transfered through ODBC. Basically it's like trying to use a bicycle to pull a trailer!!! Not the right tool for the job, however, there is a nifty way around it.

I rewrote the export process in my application to export the data to text files in a certain format and then called Oracle's SQLLDR.EXE (SQL Loader) product to transfer the data. This requires a certain level of the oracle product to be installed on the user's machine, however, my process used to run overnight (about 6 hours) transfering data, now that i've changed it, it works in about 5 minutes.

The users were very impressed!!!!

The advantage to SQLLoader is that (if you know what you are doing) you can change the commit settings to make it run fast or slower, by comiting more or less frequently.

Hope this helps, and keep me informed on you progress.

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[jmeadows7]

Have you tried using SQLPassthru so the commands/data don't go through the ODBC interpreter?

 

[nashman]

Thank you both for your responses.

TheFitz:
Did you shell the command for SQLLDR.exe and call it from VBA ? If so, what did your VBA look like ?

jmeadows7:
I am unfamiliar with PassThrough queries. Do these work for all ODBC databases or is it restricted to SQLServer databases ?

Thanks

 

[PHV]

Do these work for all ODBC databases
AFAIK yes.
You have to use the RDBMS native SQL dialect

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244