Re: Access and Oracle

"Mohammad" <mhakimjavadi_at_hotmail.com> wrote in message news:cc59b627.0310070906.740e0b99_at_posting.google.com...
> Hi
>
> Thanks for your reply and information. My code is VB and ODBC. I read
> record from Access DB and Insert the record to Oracle DB. It is more
> slow than SQLPlus.
>
> What is the ddl for the table and its related indices? I don't know
>
> What does sql trace show? what do you mean?
>
> Please advise
>
> Thanks

Are you using a recordset for the Oracle connection? Look into the cursortype property. You might be using a client-side cursor, in which case, the whole recordset resides in your pc's memory. A server-side cursor would place the burden of storage on the database server.

Also think about even further reducing the load on your workstation by translating the Access data into pure DML - an insert statement. I'm not familar with using Access as a database, but I'd imagine the ADO is the same, so: First, open a connection object for Oracle, then open a command object for Oracle. Then, open a connection and recordset to the Access data. Set up a while loop that steps through each record in the Access recordset, using MoveNext. With each record in the Access databse, call a function. The function will return a string that is an SQL insert statement containing the data from Access. When you get that string back, use the execute method of the Oracle command object to run the SQL command. After the SQL command runs, check the command object for transactional errors.

This places the entire burden of the database on Oracle- where it should be for this kind of bulk application. You don't want to keep track of any recordset, any cursors, any positioning, etc- let Oracle Server handle it. It's not as glamorous as using a recordset, but it'll reduce the load on your workstation.

In fact- bring up your workstation's performance charts next time you run your VB program. Right-click the taskbar, choose task manager, then the performance tab. Slide the graph off to the side, and run your VB program. If the numbers run high, you know that you're just using up too many resources on your machine. If you wanted to do logging, do start/run/perfmon.msc and add the necessary counters.

Oh and skip using ODBC for Oracle. Use OLE DB- unless you're dealing with an Oracle 7.x database.

-Thomas Received on Tue Oct 07 2003 - 17:08:56 CDT