Slow performance when using Linked Server on Oracle
Posted on 2010-11-13
To do a short story, I setup a linked server to an Oracle database in MSSQL 2008 using the ODBC driver provided by the Oracle client installation. Connection is good and I can browse table and data.
When I insert data using the query:
INSERT INTO [Oracle]..[SAPLQP].[ZMARKPACK] .....
it takes between 3-14 seconds to executes. Which is, of course, unacceptable, I can't stall the whole process for 3-14 sec while it writes into Oracle.
SELECT * FROM [Oracle]..[SAPLQP].[ZMARKPACK] takes around 3-6 sec.
Oracle server is oversea in Europe. Ping time is around 15-30 ms.
I wanted to replace an existing program written in VB6 installed in the MSSQL server that read the SQL table and write into Oracle using the same ODBC driver (ADODB connection). Throught this program, I can write 3-4 rows in Oracle per seconds.
Why is it so bad when I use Linked Server? It's the very same ODBC driver, pointing to the very same Oracle listener..
thanks for your time and help