Re: Access and Oracle

So clearly the problem is in Access. Why don't you turn on odbc trace and look at all the crap it is doing? Just create a small test (read 1 record from the Access DB and write 1 record into the Oracle db) and trace that. Look at what it does. My guess is that the Access program is trying to be "intelligent" and do all sorts of fancy things that are costing you a fortune in performance. I'd suggest Oracle stuff do do, but the problem is not in Oracle.(eg sql trace, explain plan, tkprof, but you can't even give us the version of Oracle, the OS it is on, etc. so lets start very basic.)

Access is a nice play toy and it does a lot of things for you which is often the problem. It piles a bunch of layers on and potentially does things for you that ae highly inefficient. One other poster suggested using pass through querys. This is an excellent suggestion. It reduces the games Access will play with your query. I've seen different drivers make a difference. In one case we did a join to retrieve some results and it was fast. We changed drivers and did the same operation and the client crashed. (the other driver was retrieving both tables into the workstation's memory and trying to join them there - which is stupid since both tables had millions of rows and we were going over a 56K line.) Jim

"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
>
>
> "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in
message news:<RZdgb.509586$cF.181282_at_rwcrnsc53>...
> > That is really not very much information. Try setting the
_run_faster=true
> > in the ODBC.ini file. How fast is the same operation in SQLPlus? Is it
> > fast or slow? How fast or slow is it? What is the code? Are you using
> > bind variables (Bet you are not.)? What is the explain plan? What is
the
> > ddl for the table and its related indices? What does sql trace show?
> > Jim
> >
> > "Mohammad" <mhakimjavadi_at_hotmail.com> wrote in message
> > news:cc59b627.0310060501.61f69086_at_posting.google.com...
> > > Hi
> > >
> > > I have to read information from Access DB and insert
> > > record to Oracle9i table. I used VB and ODBC for Access
> > > and Oracle and I did that. It is working find but it is
> > > working very very slow!!!!!
> > >
> > > Could you please tell me any other way to do that job and
> > > faster or maybe I missed same setting for ODBC.
> > >
> > > Thanks for your help.
Received on Tue Oct 07 2003 - 20:38:09 CDT