November 18, 2004 at 1:38 pm
I have 2 linked SQL servers A & B. In the fisrt case I select data from 2 tables on the linked server A and I'm executing a remote query on server B (The name of linked servers are input parameters). Here is the query:
SELECT
JP.JID,
JP.PrID,
JP.SequenceNumber,
JPB.CoinRate,
JPB.CoinAmount,
JP.ResetAmount,
JP.RollbackAmount
FROM ' + @LinkedServerA + '.dbName.dbo.tblJP JP
INNER JOIN ' + @LinkedServerA + '. dbName.dbo.tblJBuc JPB
ON JP.JID = JPB.JID
WHERE JP.JStop IS NULL
As I’m running this on server B – where the processing will occur – on server A or locally on Server B?
In the second case I’m joining data from the 2 servers. Example:
SELECT
JP.JID,
JP.PrID,
JP.SequenceNumber,
JPB.CoinRate,
JPB.CoinAmount,
JP.ResetAmount,
JP.RollbackAmount
FROM ' + @LinkedServerA + '.dbName.dbo.tblJP JP
INNER JOIN ' + @LinkedServerA + '. dbName.dbo.tblJBuc JPB
ON JP.JID = JPB.JID
LEFT OUTER JOIN ' + @LinkedServerB + '. dbName.dbo.tblBBB BPB
ON BPB.ASID = JPB.ASID
WHERE JP.JStop IS NULL and bpb.status = ‘GOOD’
Where the query will process this time as I’m having filtering on both sides?
The problem is that one of the tables has millions of rows and the other side is much smaller… Is there a way to force this execution to run on a particular side?
Thanks,
MJ
November 18, 2004 at 8:32 pm
When execute a remote query, what SQL Server does is its bring down all the rows from the remote server to local server and then applies the filters. So it doesn't make much difference whether you have where clause or not.
To do true remote execution, you should use OPENQUERY method in which case it will just transfer entire query to remote server, executes there, and only bring downs the required rows. If its really large table which you are using in join, I will prefer to create temp table locally, insert the rows from the remote server into temp table (I would still use OPENQUERY for this insert) and then use temp table with local table in the join. It works faster, thats my experience.
November 19, 2004 at 2:01 am
yes, OPENQUERY may force it to execute as remote, but ... you must be very good at chosing what you wanna do
SQL Server has the posibility of runnig a subquery . So the select should be grouped by server , added the maximum filtering, then join the local like :
select a.col1, b.col2 FROM LocalTable a
inner join (select b.col2, c.col3 from RemoteTable1 b inner join remoteTable2 ON ... WHERE b.... AND c.... ) b ON ...
WHERE a.... AND b....
It must be mentioned that the Standard edition works poorely.
Daniel
November 19, 2004 at 9:48 am
Thanks, guys!
This is what MS confirmed but I was seeing something else going on...
If I tried to run the first query above, the activity on the remote server A was jumping up crazy... If all processing is on Sever B then why server A is getting so busy? And I tried several times...
How running the query on the local server affects the remote machine and why?
With appropiate indexing all joins between the 2 servers run pretty fast...
MJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply