Post reply

linked server 2 oracle, inserting a row

  • June 9, 2004 at 2:48 am

    #85821

    Hi,

    I have setup an linked server from SQL Server 2000 to Oracle 8i installation using MSDAORA.

    I have no problems using select statements, and indeed insert, updates, and deletes complete as expected.  However, when inserting, updating and deleting the execution time is excessive.  Looking at the trace file on Oracle it seems to be doing a select statement before every insert, update and delete. This returns every row and every column in the table I wish to amend, and therefore adds a huge amount of overhead.

    When running this query directly using an oracle client tool this does not happen.  Why is this happening and how can I stop it?

    Thanks in advance!

  • June 9, 2004 at 9:08 am

    #509670

    Make sure fields you filter are indexed and then

    Books on Line, Configuring Linked Servers:

    "Indexed Access

    SQL Server can use execution strategies that involve using the indexes of the Index provider to evaluate predicates and perform sorting operations against remote tables. Set the IndexAsAccessPath provider option to enable indexed access against a provider.

    Additionally, when using indexes involving character columns, set the collation compatible linked server configuration option to true for the corresponding linked server. For more information, see sp_serveroption.

    "

  • June 9, 2004 at 11:26 am

    #509697

    You can prevent this by using the openquery functionality...

    First you tell it to insert, then the openquery, actually provide a query (one that will return a single row is best), then the insert values, for example..

     

    INSERT OPENQUERY(ORACLELINKEDSERVER, 'SELECT COLUMN_A FROM TABLE WHERE COLUMN_A = ''THIS VALUE''') VALUES ('RECORDS TO INSERT')


    Shamless self promotion - read my blog http://sirsql.net

  • June 10, 2004 at 5:09 am

    #509814

    I had indexes on all my where conditions, and they were all on numeric columns, (so no problem with collation).  SQL Server used the indexes on oracle for the select statements but not for insert, updates or deletes.  It was passing back the whole table then SQl server was doing the filtering, which in turn made the execution time a lot longer. 

    Using the openquery works really well , except that I cannot put my update or delete queries in a stored procedure as I cannot find away of passing parameters to it.  Insert statements will be ok as you do not need the openquery statement to return the row you are working on.

    However, trying to fix this problem myself before the above post, I changed settings for the OLE DB provider, and used sp_serveroption to change other options on the linked server. I was attempting to improve performance, but mostly it stopped select statements from working.  I have changed all the settings back to how they were but now my select statements arent using the indexes either and I REALLY dont want to have to put them into openquery statement.  Any ideas what I have broken?!

  • June 10, 2004 at 5:21 am

    #509817

    You can put the inserts, updates and deletes within a proc, you just have to build and execute a dynamic sql statement to do so...

     

    CREATE PROC INSERT_ORACLE_DATA @INSERTVALUE VARCHAR(25)

    AS

    DECLARE @SQL VARCHAR(2000)

    SET @SQL =

    'INSERT OPENQUERY(ORACLELINKEDSERVER, ''SELECT COLUMN_A FROM TABLE WHERE COLUMN_A = ''''THIS VALUE'''') VALUES (' + @INSERTVALUE + ''')'

    --PRINT @SQL

    EXEC (@SQL)

     


    Shamless self promotion - read my blog http://sirsql.net

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

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