Linked Server - OPENQUERY Performance Issue

  • Hi,

    I hope you can help.

    I have been tasked with developing a solution to populate a sql db with data from an oracle db.

    I've been trying to use a Linked Server to do this and have so far been experimenting with using OPENQUERY. For small tables it works well. But on tables with millions of rows however it is unworkably slow.

    The INSERT statement I'm using seems as basic as it gets:

    INSERT INTO SQL_DB.batch_transaction_analysis

    (A

    , B

    , C

    , D)

    (Select SELECT A

    , B

    , C

    , D

    FROM OPENQUERY(ORACLE_DB,'SELECT A

    , B

    , C

    , D

    FROM batch_transaction_analysis')

    Is there a better alternative to using OPENQUERY for selecting million rows of data across a linked server?

    Or even a better way of writing the insert query itself that might speed things up?

    Hope you can help!

    Thanks loads

    lins

  • For loading millions of records, it's better to use SSIS. You'll have good performance benefits.

    Linked servers are good for less amount of data.

    --

    SQLBuddy

  • Thanks for the response. SSIS it is then!

    cheers

    lins

  • lindsayscott23 (4/1/2014)


    Thanks for the response. SSIS it is then!

    cheers

    lins

    Yep. You are welcome, lins 🙂

    --

    SQLBuddy.

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

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