April 1, 2010 at 10:40 am
The following result in a ridiculously slow remote scan:
UPDATE [RemoteSQLServer].[Remote].[dbo].[InvoiceI]
SET Line = src.Line
,Quan = src.Quan
,fDesc = src.fDesc
,Price = src.Price
FROM [RemoteSQLServer].[Remote].[dbo].[InvoiceI] dst
INNER JOIN InvoiceI src ON dst.InvoiceID = src.Ref
AND dst.Line = src.Line
INNER JOIN Invoice I ON src.Ref = I.Ref
INNER JOIN Loc L ON I.Loc = L.Loc
INNER JOIN Owner O ON L.Owner = O.Id
WHERE O.Ledger = 1
AND dst.PortalsID = 1000
The remote table is indexed with:
CREATE UNIQUE NONCLUSTERED INDEX [idx_InvoiceI_PortalsIDInvoiceIDLine] ON [dbo].[InvoiceI]
(
[PortalsID] ASC,
[InvoiceID] ASC,
[Line] ASC
) ON [PRIMARY]
If I replace the above with a delete and reinsert the performance is just fine.
Any suggestions?
Currently the remote server is sql server 2000. Would this resolve itself with 2005 or 2008 on the remote?
Thanks,
Karl
April 1, 2010 at 4:49 pm
I guess you update the whole remote table, not only records you want to update.
Try this:
UPDATE dst
...
_____________
Code for TallyGenerator
April 1, 2010 at 5:15 pm
Karl Kieninger (4/1/2010)
Currently the remote server is sql server 2000. Would this resolve itself with 2005 or 2008 on the remote?
There is a high probability of it self-resolving by upgrading. The query optimizer is much better in 2005 and 2008.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2010 at 6:02 pm
One thing to try would be to do all the joins to the local tables first and populate variables with the information, then send across the update to the variables without all the joins. You could take this one step further by creating a SP on the remote server that updates the table based on the passed in parameters and then call that procedure using the variables that you populated.
No promises, but it's something to try.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply