July 19, 2005 at 9:07 pm
Hi,
This is just a message from the trenches - in researching the issue in this forum I saw many messages on the subject but no specific advice on a fix. The problem is that if your execute a query against a linked server with a WHERE clause all the data from the linked servers is passed down the wire to the local machine where the WHERE is executed to deliver the required subset.
The fix to this problem was as follows:
1) Make sure RPC is turned on for the linked server.
2) My sql code to return all transactions relating to a pallet is:
CREATE PROCEDURE [dbo].[stp_getHIDToImport]
@PLTID As Uniqueidentifier,
AS
DECLARE @VAR char(40)
SELECT @VAR = @PLTID
EXEC Remote_01.tms122.dbo.sp_executesql
N'Select * from tms122.dbo.tblHIDHides where HID_PLTID = @PLTID',
N'@PLTID char(40)',
@VAR
This return about 60 rows per query rather than 2.6 million before the above change.
I don't pretend to understand the syntax - I just know it works.
Peter
July 20, 2005 at 7:09 am
distributed/linked queries have a high obverhead, if at all possible I only make rpc calls - I would have a proc ( query ) on the remote server and use a local proc to pass the parameter to the remote proc - take it from me this is more efficient and far quicker!!
btw. ms technet say that remote queries carry a minimum 30% extra overhead.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply