Re: Very long to to insert a row in an Oracle linked server

On Thu, 5 Jul 2007 22:06:03 -0700, Peter Jones
<PeterJones@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi,

I have an Oracle database connected into a SQL Server 2000 database as a
linked server. The connection works fine except inserts are very very slow. I
originally was using the OLE/DB for Oracle and an insert took 25 seconds per
row. I changed the linked server interface to ODBC and created an ODBC
interface and that reduced the insert time to 10 seconds per row (I've no
idea why ODBC was better).

I wonder if it could be a version mismatch between the driver and the
database?

If the insert code is run on the Oracle machine we get about 10 inserts a
second.

This sounds rather slow to me if you are executing the query locally
on the server. Do you have a lot of indexes on the table(s)? What
does EXPLAIN PLAN show you in Oracle about your query execution
paths?

I turned on ODBC logging but is doesn't give any timings and I don't
understand what its is logging anyway.

The ODBC trace function slows things down dramatically; make sure
this is turned off. I remember some occasions when it took a couple
of tries to get it to really turn off, so double-checking this
wouldn't hurt.

As to the rest, it might be a network problem. Try doing bulk inserts
instead of single rows, and make sure you use bound variables in the
query. There are so many possibilities, it is sometimes hard to pin
down.

--
Bob Hairgrove
NoSpamPlease@xxxxxxxx
.

Relevant Pages

  • Re: Set up an ODBC connection Programmatically?
    ... the correct DSNless connection string for Oracle? ... Doug Steele, Microsoft Access MVP ... Other people can use this database, ... they have their system ODBC drive set up exactly as mine was when I ...
    (microsoft.public.access.modulesdaovba)
  • Re: Please Reply . Its Urgent.
    ... But i'm still not able to connect to Oracle Database either through ODBC ... Oracle in Ora9i and testing the connection) as follows:(recent error ...
    (microsoft.public.vb.database.ado)
  • Re: Problem with numeric blank fields
    ... Oracle 8i and ASA 8.03. ... I checked the ODBC log to find out what ODBC types they were bound to. ... which type of database? ... thing I can think off is to modify the SQL directly in MS Query (i.e. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Connectivity between Oracle and Progress Database
    ... Forse che io mi connetto ad oracle, faccio delle query ed il server stesso ... Una sorta di DBLink utilizzando gli ODBC? ... > am new to progress database. ...
    (comp.databases.oracle.server)
  • Re: MS OLE DB for ODBC with Oracle ODBC source headaches
    ... John Bell wrote: ... the Oracle OLEDB provider (as opposed to the Microsoft OLE for ODBC ... >> I have no idea why sometimes I can connect to the linked server with no ...
    (comp.databases.ms-sqlserver)