Linked Server Query
-
Hi,
Can someone interpit the following behavour for me.
The following scirpt is a modified extract from a longer SP that runs nightly and refreshes a Remote Database via LinkedServers with changes that occurred since the script was last run. I have modified the script to set the Last run date to the currentSystem time as I know no updates have occurred therefore no updates need to be pushed to the remote server. I expected this script to run in seconds yet it takes 10 minutes. The tables in question have 1 million rows and I need to push the data out to the remote servers
declare @LastRunDate datetime
set @LastRunDate = getdate()
UPDATE ProductionDB.Catalog.Dbo.Product
SET Description = remoteProduct.Description,
SequenceNo = remoteProduct.SequenceNo ,
Side = remoteProduct.Side ,
Price = remoteProduct.Price,
UpdatedDate = remoteProduct.UpdatedDate
FROM ProductionDB.Catalog.Dbo.Product remoteProduct
INNER JOIN CentralDB.dbo.Product as ProductMaster
on ProductMaster.ProductID = remoteProduct.ProductID
WHERE ProducTMaster.UpdatedDate > @LastRunDate
Looking at the execution plan I can see that the 1 million rows are returned from the remote server to the central server, sorted and then compared to the rows return from the productmaster table which is zero. I have read that all updates are performed locally so therefore this behaviour is as expected. However here is the weid bit of the 15 tables that I update this way some return all rows and take minutes to execute others return no rows from the remote server and execute in under 1 second. Also if I remove the variable @LastRunDate and replace it with the fn GetDate() as in
WHERE ProducTMaster.UpdatedDate > GetDate(), the query executes in under 1 second as no remote rows are returned. Anyone know why this is ? Why using the variable causes all rows returned and yet getdate() doesnt ? Any help appreciated.
Cheers
A
-
It may be parameter sniffing at work. However I think that the simplest workaround would be to do something like this :
IF EXISTS (Select * from dbo.ProducTMaster WHERE ProducTMaster.UpdatedDate > @LastRunDate)
begin
update...
end
-
I agree and thats the way I will be implementing it. However it would mean that if 5 rows in the master copy changed then all remote rows are copied over to the local and then the 5 rows are sent back to be updated. I suppose I could insert the rows that have changed into a tmp table on the remote db side and then call a remote sp to update the changes on the remote side and then delete the changes. However I would like to understand why my existing script is behaving as it is.
-
Sql server HAS TO import the data for the join. There's no way around that (unless you do as you said and send the new data to the other server and let him do the work.
-
I have modified my sp and now i pass in @lastRunDate as a parameter and it works fine. As in the sp now runs in 3 sec where no updates exist compared to 20 before. However If I set the date to 3 days back and get 3 days of updates I get a new error.
Server: Msg 7343, Level 16, State 4, Procedure tester, Line 16
OLE DB provider 'SQLOLEDB' could not UPDATE table '[RemoteDB].[Catalog].[Dbo].[Product]'. The consumer could not delete the row. A deletion is pending or has already been transmitted to the data source.
[OLE/DB provider returned message: Cursor operation conflict]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::SetData returned 0x80040e23: The consumer could not delete the row. A deletion is pending or has already been transmitted to the data source.].
-
Never seen that!!
-
That's kind of a kewl error.
I don't think I've actually seen it either. What version of MDAC are you on? You might want to consider updating the MDAC if you're on anything earlier than MDAC 2.8. Let us know what happens with this.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Are version is MDAC 2.80.1022.0.
No idea whats causing this error. I am stumpted.
-
I found this post and low and behold it worked. http://dbforums.com/t507548.html. SET NOCOUNT was set on at server level on both servers. I turned this off at server level re-ran my update and it worked fine. Hopefully my complete script will run smoothly.
-
Testing my complete update script and while its working, I now noticed through the profiller that for one table where as before it fetched 100 rows at at time from the remote server it now fetches 1 at a time. aaaaggggh (pulling hair out of head)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply