December 4, 2007 at 2:26 pm
Hi All,
apologies if this is not the right forum. If its not if someone can tell me where I'll repost. thanks.
Problem: I have a query being executed in an SSIS package.
Query is an update, set = select construct.
Part of the select is a reference to a remote server.table and then it joins back to a table in the local db.
Performance is very slow - over 2 hrs before I had to terminate. Equav syntax run in the sql 2k installation run sin under 30 secs.
I have set the collate parameter to trus in the linked server definition on both servers sql 2k5 and 2k.
can some one suggest a better way of doing this.
I have run through - leave the dts package as is.
set up corresponding keys on the tables concerned.
looked at openquery but can make it work.
Help please!
UPDATE dbo.s9ge
SET s9ge.institutionname = ( SELECT tid.surname
FROM [hosmis01].ams.dbo.tedinstitution ted
INNER JOIN [hosmis01].ams.dbo.tidentity tid ON ted.identitynbr = tid.identitynbr
WHERE ted.edinstitutionid = FS.dbo.s9ge.edinstitutionid
)
WHERE s9ge.institutionname IS NULL
s9ge local table
FS is a linked server - sql 2k installation
this sql is run/initiated locally on a sql 2k5 installation
thanks
December 4, 2007 at 3:21 pm
Hi All,
solved this problem using the remote syntax to force the remote query to be completed on the remote host.
UPDATE dbo.s9ge
SET s9ge.institutionname = ( SELECT tid.surname
FROM [hosmis01].ams.dbo.tedinstitution ted
INNER remote JOIN [hosmis01].ams.dbo.tidentity tid ON ted.identitynbr = tid.identitynbr
WHERE ted.edinstitutionid = FS.dbo.s9ge.edinstitutionid
)
WHERE s9ge.institutionname IS NULL
query now runs in 11 seconds
December 11, 2007 at 7:40 am
Out of curiosity, wouldn't the following have done the trick for you too?
UPDATE s9
SET
institutionname = x.surname
FROM dbo.s9ge s9
INNER JOIN (
SELECT
ted.edinstitutionid,
tid.surname
FROM [hosmis01].ams.dbo.tedinstitution ted
INNER JOIN [hosmis01].ams.dbo.tidentity tid ON (ted.identitynbr = tid.identitynbr)
) x on (x.edinstitutionid = s9.edinstitutionid)
WHERE s9.institutionname IS NULL
January 8, 2008 at 8:52 am
I had similar issues with running linked server queries. I ended up writing stored procedures on the linked server, and called the stored procedures instead of running the queries remotely. This speeded up the process. I'm not sure, but my theory is that the remote queries were table scanning, but when I put the queries into stored procedures on the linked server, the queries used index seeks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply