Linked Server - OPENQUERY Performance Issue

• lindsayscott23

  SSCrazy

  Points: 2339

  More actions

  March 28, 2014 at 5:52 am

  #291552

  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

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  March 28, 2014 at 12:00 pm

  #1701264

  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

• lindsayscott23

  SSCrazy

  Points: 2339

  More actions

  April 1, 2014 at 5:10 am

  #1701968

  Thanks for the response. SSIS it is then!

  cheers

  lins

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  April 1, 2014 at 9:23 am

  #1702097

  lindsayscott23 (4/1/2014)

  ---

  Thanks for the response. SSIS it is then!

  cheers

  lins

  Yep. You are welcome, lins 🙂

  --

  SQLBuddy.