July 31, 2012 at 3:20 pm
I'm having some performance issues updating some rows on a linked server. The query is only updating 7500 rows but has been executing for over 15 minutes. The rows will eventually update, but this seems a bit rediculous for this transaction to run this long.
I am a newbie when it comes to linked server transactions, so please bear with me, but does anyone see any glaring issues in the below T-SQL? There are NO uncommitted transactions
SET XACT_ABORT ON
BEGIN TRAN
UPDATE [LinkedSQLServer].GoldMine.dbo.CONTACT2
SET U_MAILLIST = Data.MAILINGLIST,
U_MAILDATE = Data.DATESENT,
U_EST_DEL = (Data.DATESENT + 10)
FROM MailingData.dbo.Data
WHERE CONTACT2.ACCOUNTNO = MailingData.dbo.Data.ACCOUNTNO
AND DATESENT = CAST(CONVERT(varchar(8), GETDATE(), 1) AS datetime)
AND KEY1 = 'AD'
July 31, 2012 at 4:11 pm
Anytime you do a join between a local table and a remote table you have a likely performance problem.
The best way to deal with this depends on many things, like data size, selection criteria, table structure, network bandwidth, etc.
Most peple avoid this whenever there is a reasonable alternative, like replication, SSIS data copy, BCP, etc.
July 31, 2012 at 7:22 pm
I faced a similar issue recently when trying to insert records into a linked server. I ended up creating a stored procedure on the linked server to do the insert. I then called the procedure from the local server and got dramatic performance improvements.
It seems that pulling data from the linked server can perform quite differently than pushing. I suspect this is true of updates as well as inserts.
Perhaps this can help you. Of course you will need to create a linked server in the opposite direction.
July 31, 2012 at 7:34 pm
Also I might try experimenting with putting your join criteria in a proper join statement between the two tables.
See how long a SELECT takes when joining: Air code to follow.
SELECT U_MAILLIST
FROM MailingData.dbo.Data
INNER JOIN [LinkedSQLServer].GoldMine.dbo.CONTACT2
ON CONTACT2.ACCOUNTNO = MailingData.dbo.Data.ACCOUNTNO
AND DATESENT = CAST(CONVERT(varchar(8), GETDATE(), 1) AS datetime)
WHERE KEY1 = 'AD'
What datatype is DATESENT? Why are you converting getdate() to VARCHAR and then back to DATETIME?
Does the following not return any records?
SELECT U_MAILLIST
FROM MailingData.dbo.Data
INNER JOIN [LinkedSQLServer].GoldMine.dbo.CONTACT2
ON CONTACT2.ACCOUNTNO = MailingData.dbo.Data.ACCOUNTNO
AND DATESENT = GETDATE()
WHERE KEY1 = 'AD'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply