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 |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-09-22 : 14:15:29
|
| Hi,We are testing performance of linked server between SQL Server- Oracle and SQL Server-Sybase and not clear about how it actually works.We are selecting between 10k-10M rows from Oracle or Sybase database and Insert into the SQL Server database and both the databases are on separate physical machines. We see that SQL Server machine is using all the memory it finds and performance degrades as the batch size increases. How does the linked server internally works. Does it copy a batch of rows into memory and insert into SQL Server database and do this iteratively and if yes then what is the default batch size or it tries to copy all the rows to memory and then insert into the database?Thanks--Harvinder |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 14:17:04
|
| It works depending on the driver that you selected. Whatever driver you selected, you'd have to investigate what it is doing internally.Tara |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-09-22 : 14:50:01
|
| Use the oracle provided OLE DB provider instead of the microsoft OLE DB for Oracle. You will get much better performance.also, this seems like something you should do with BCP rather than using a linked server. You could even create a DTS package that would perform better than the linked server.-ec |
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2004-09-22 : 14:54:30
|
| I tried using oracle oledb but always getting invalid column specification and didn't find much help on google so went back to mirosoft odbc for oracle.... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-09-22 : 15:05:09
|
quote:
Originally posted by rubs_65
I tried using oracle oledb but always getting invalid column specification and didn't find much help on google so went back to mirosoft odbc for oracle....
What version of the Oracle client are you using? Did you download the updated version of the OLE DB from oracle't technet website?technet.oracle.com is where you can find the updated versions.-ec |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-09-24 : 01:12:05
|
| Also be sure to look at the "collation compatible" setting on the linked server; that can have a big effect on which queries are run on the linked server, and which queries pull entire tables and apply where's and whatnot locally.Cheers-b |
|
|
|
|
|
|
Linked server Internals