Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server - OPENQUERY Performance Issue Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 5:52 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 14, Visits: 128
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

Post #1555845
Posted Friday, March 28, 2014 12:00 PM This worked for the OP Answer marked as solution
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:14 PM
Points: 1,194, Visits: 2,217
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
Post #1556093
Posted Tuesday, April 1, 2014 5:10 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 14, Visits: 128
Thanks for the response. SSIS it is then!

cheers

lins
Post #1556911
Posted Tuesday, April 1, 2014 9:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:14 PM
Points: 1,194, Visits: 2,217
lindsayscott23 (4/1/2014)
Thanks for the response. SSIS it is then!

cheers

lins


Yep. You are welcome, lins

--
SQLBuddy.
Post #1557058
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse