Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rockmoose
SQL Natt Alfen
3279 Posts |
 Posted - 2005-02-01 : 09:21:20
|
Why is linked server to Oracle so slow ???INSERT OPENQUERY(ORADEBACLE,'SELECT * FROM xxx.TableX')SELECT * FROM dbo.TableXINSERT ORADEBACLE..xxx.TableX(col1,col2,...,col40)SELECT col1,col2,...,col40 FROM dbo.TableXBoth queries take about 5 minutes!, yes minutes Edit: for 2011 rows.rockmoose |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-01 : 20:41:51
|
| Basically, an OLE DB process such as this is using...you guessed it, a CURSOR to do the work. If it doesn't actually define a server side cursor, it uses an internal cursor to perform JOINs and INSERTs. Doing the insert over an OPENQUERY/linked server also has extra overhead from MSDTC.How about exporting to a text file and bulk loading it? Oracle has tools that can do that. No need for a transaction either. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-02 : 01:42:10
|
| Using textfiles would be the next logical step.We will just have to negotiate the setup with the oracle DBA, but it shouldn't be a problem.If they want speed they will get text files, else they will have to be very patient ;)rockmoose |
|
|
|
|
|
|
slow oracle linked server