September 10, 2007 at 8:07 am
I have a stored procedure in SS2k that queries a linked server (also SS2K) the query joins a few tables on the remote server and sends the results.
The problem is the excution plan shows SQL Server bringing back the entire contents of one of the tables in the join and then performing the join on the local server.
This causes the query to run for around 1 minute instead of about three seconds if the joins are performed by the remote machine because 20k rows are transferred back from the remote server even though the actual results set is only about 300 rows total.
If I create a view with the same query on the remote server and query that through a linked server it returns in 3 seconds with all the joins performed on the remote side.
This is what the query in the SP looks like:
select
convert(varchar,TransactionEndDate,101) BuyDate,
TransactionEndDate BuyDateTime,
rtrim(transactionnumber) transactionnumber,
BuyRegister, PaidOutRegister,
s.firstname BuyerFirstName, s.lastname BuyerLastName
,r.firstname POBuyerFirstName, r.lastname POBuyerLastName,
buyitemtotal,
cashbuytotal,
checkbuytotal,
giftcardbuytotal,
DeferredPaymentPaid,
DeferredPayment,
ApplyBuyToSale,
GCVoidedForCash,
buytosale,
rtrim(v.vendorname) vendorname,
rtrim(v.phone) phone
from ods.dbo.transactionheader t
inner join pos.dbo.salesper s on
t.Buyersalescode = s.code
left join pos.dbo.vendor v on
t.vendornumber = v.number
left join (Select code, firstname, lastname from pos.dbo.salesper) r on
t.PaidOutSalesCode = r.code
where transactionenddate >= @StartBuyDate
and transactionenddate <= @EndBuyDate
and buyitemcount <> 0
and voidbuytotal = 0
The table causing issues is 'vendor'.
Is there any way to force the execution plan to do the join on the remote server? Any other ideas to make this work? My last resort is to create a view at the remote site but I really want to avoid that because then I have views to maintain across all my remote sites.
Thanks in advance for your help,
Ben
September 12, 2007 at 12:24 pm
1) Create a stored proc that lives on the remote server. You'll avoid the RPC call that way.
and call it like this Exec servername.databasename.dbo.procname
2) Index join and where clause fields.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply