April 18, 2011 at 12:28 pm
would there be any performance difference have a link server between 2 sql server using server type as Other data source("Microsoft OLEDB for SQL Server") versus SQL Server.
April 19, 2011 at 7:10 am
You'd have to test to find out. I've used both and not had problems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 20, 2011 at 7:33 am
Tara-1044200 (4/18/2011)
would there be any performance difference have a link server between 2 sql server using server type as Other data source("Microsoft OLEDB for SQL Server") versus SQL Server.
I would suspect that any performace difference would be overwhelmed by the fact that you are using linked servers in the first place. They are notoriously bad for performance!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2011 at 8:19 am
What do you suggest to pull data from DB's in other servers then.?
April 20, 2011 at 8:39 am
Joy Smith San (4/20/2011)
What do you suggest to pull data from DB's in other servers then.?
I have had great success at clients using replication to help avoid cross-server data access (especially joins). This can be an especially big win when it is master dictionary/lookup data that is relatively static but needs to be accessed on many machines.
Outside of that, there are still a variety of approaches that can be used to help mitigate linked server query perf problems.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 21, 2011 at 9:37 am
It's a good solution to use replication but it's very easy to get lost in it.
If you go down that road, my two suggestions are to keep a graphical schema of what goes where and not to make tables replicate in both ways. Each table replacating in one direction only will simplify your job if you have synchronisation issues.
___________________________________
I love you but you're standing on my foot.
April 22, 2011 at 5:34 am
There are a couple of things you can do. As stated, Link Servers can be really bad performers. We have them, I am slowly removing them.
Replication is a good solution, and we were using it here, but it was snap shot not transactional. I turned all of that off, to much of a hassle maintaining it, and the snapshots only occurred once a day.
Transactional replication may be what you need, depending on how real time you need.
I replaced the replication with SSIS. In my situation it actually works better.
Most of my data that has to be moved is static, so I move most of it once a day, but I have some that moves hourly. SSIS works great for that.
Where I absolutely have to use a Linked server, I try and get the developer to use the OpenQuery option. Unless the query needs a join across the linked server, this works way better than just the normal linked server query.
Leonard
April 22, 2011 at 7:37 am
Lots of good advice here. The one thing I learned back when I used linked servers is that you should do as much work on the linked server as possible. That meant that I often wrote stored procedures on the linked servers that returned just the data I needed. That typically made performance much better. I'd avoid joining across a linked server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 22, 2011 at 8:35 am
Jack Corbett (4/22/2011)
Lots of good advice here. The one thing I learned back when I used linked servers is that you should do as much work on the linked server as possible. That meant that I often wrote stored procedures on the linked servers that returned just the data I needed. That typically made performance much better. I'd avoid joining across a linked server.
Jack, what you mention is definitely high on my list of "variety of approaches" that I mentioned previously. A very effective solution for numerous scenarios.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply