Post reply

insert to Oracle db takes time from SQL2k

  • June 5, 2003 at 11:26 am

    #105867

    We have defined linked server in EM. I am executing a SP, which does a select in sql2k tables and inserts into oracle table. The select alone runs less than a minute, but the insert to oracle takes 8 to 10 minutes. Any idea, where to look to solve this issue?

    Thanks in advance.

  • June 5, 2003 at 11:28 am

    #460015

    By the way, the Orcle server is in Unix solaris, in the network.

  • June 5, 2003 at 12:38 pm

    #460027

    Guess what.. I tried inserting one row to the oracle table from QA, it is taking 7 mts.

    Is MSDTC service really slow in handling orcle transactions?

  • June 6, 2003 at 12:52 am

    #460099

    I've had Oracle (linked server) update problems before too. In that case it was because the MS driver for Oracle was great at getting data from Oracle, but wasn't too flash (ie. terrible) at putting it back. We switched to an Oracle-supplied driver and got much better performance.


    Cheers,
    - Mark

  • June 9, 2003 at 4:30 pm

    #460484

    mccork, thanks for your reply. I created an instance in my local computer (win prof os), and connected to the same sun solaris oracle server. I have oracle81 client in my machine. The simple select query took 57 seconds. But, from my development server (win 2000) using orcle client 81 it takes a standard 7.29 minutes for the same query.

    I have modified the registry keys as per MS suggestion:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

    "OracleXaLib"="oraclient8.dll"

    "OracleSqlLib"="orasql8.dll"

    "OracleOciLib"="oci.dll"

    What should I do to use the oracle driver instead? thanks in advance.

  • June 9, 2003 at 8:13 pm

    #460494

    Sorry but you've caught me out... when I said "We switched to an Oracle-supplied driver" I meant that someone else in the team did the work while I shared the glory of improved performance .

    Can anyone else help out bj007 here?


    Cheers,
    - Mark

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply