February 13, 2009 at 5:23 am
In my organization, we have several linked servers that we setup to join two different types of databases existing on different servers. We run into an issue whereby in our T-SQL JOIN Statements to the linked servers were blowing up, so one of my colleagues came up with the idea of creating a view with a nolock on the linked server and then calling that view on the linked server in the main stored procedure. Please see below for example: -
Create view vw_testviewonlinkedserver --created on the linked server
(
Select * from tblProperTable (nolock)
)
--below is the main stored proc
create procedure dbo.test
Select * from tblResume tr
inner join vw_testviewonlinkedserver vwt on vwt.custid=tr.custid
The above works perfect except that in some cases, the table being called in the view has over three gazillion rows of data and causes several performance issue and causes the execution time to increase.
Please is there a better way of going about this? Help!
February 13, 2009 at 5:44 am
Hi Sam
There are various factors that affect his, network , index etc, to start with i will check the index on both the tables, and then check the network, can i assume that this is not a problem always, it only occurs time to time ???. if this is not giving you any result, get the smaller table across network and then use this on SP 😀
February 13, 2009 at 6:07 am
The main problem is to join two tables from two servers, you have to move the entire contents of one of the tables to the other server. So as you say, when it's a gazillion rows, it's slow. Most of the time when I've dealt with linked servers it's to Oracle, so the rules might be a bit different, but I'm sure the concepts are the same. We used queries, with parameters, on the remote servers to load to temporary tables and then joined the temporary data to our other data. We also used OPENROWSET which puts the processing on to the remote server rather than moving all the data locally. Performance went up considerably using this method.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2009 at 8:22 am
Thanks for the reply Grant! I really appreciate it. So pretty much if I want to stick with using the linked servers then I need to incorporate the openrowset function. If so, do I create the view on the linked server with the openrowset or I need to add the openrowset on the main stored procedure?
Thanks,
Sam
February 13, 2009 at 8:27 am
Hello CrazyMan,
This is something that we just started incorporating into most of our linked server scripts. We have not really looked into it that much to check for any consistencies. Also, getting the smaller table across the network would be more work because those databases in that network are being updated constantly and are very huge in size. We currently prefer using the linked server since we don't have that much of workforce to maintain both copies of data. Thanks for your input on this issue. Any advise from you would be much appreciated.
Thanks,
Sam
February 13, 2009 at 8:34 am
sam (2/13/2009)
Thanks for the reply Grant! I really appreciate it. So pretty much if I want to stick with using the linked servers then I need to incorporate the openrowset function. If so, do I create the view on the linked server with the openrowset or I need to add the openrowset on the main stored procedure?Thanks,
Sam
We did everything through the stored procedure because that's where the parameters were coming in that we passed through to the other server. If you just put OPENROWSET in a view and then pass parameters to the view, you're still back to where you were. All the data will have to be moved across the wire, then the parameter is applied.
Still, testing locally is something you should always do. Don't assume what worked for will work for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2009 at 11:44 am
Okay. Thank you. I will try a couple of scenarios. I appreciate all your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply