Swamp_Thing
asked on
Query with Linked Server timeout
I have a MS/SQL (2008 R2) Agent Job with several steps that use a local table in the WHERE clause of a select against a linked server. On the steps that deal with smaller results sets there are no issues but the step(s) with a large (potential) results set are timing out. I believe that the "FROM <linkedserver>.<database>. <schema>.< table> WHERE CLAIMID IN (<select calimid from local table> where <criteria>) is returning the full results set (ie: all rows in the table) from the linked server and then applying the filter (WHERE clause). The step is coded to "EXEC <local storedprocedure>" and the local stored procedure contains the query. I have read where EXEC can push query execution off to the linked server using "EXEC (<userdefinedprocedure>) AT <linkedserver>" (see:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec527f63-d454-4b8a-81e9-23a2ff54c1ca/execute-at-with-dynamic-linked-server-name?forum=transactsql ).
When I execute just the SELECT portion of the INSERT INTO statement from the stored procedure using TSQL I get 54K rows returned in 22 minutes.
I don't know if - or how - to use this when one table is local and the other is on the linked server and would like the Experts suggestions.
Here's the stored procedure:
INSERT INTO <localtable>
SELECT WC.*
FROM <linkedserver>.<remotedata base>.<sch ema>.<remo tetable> AS WC
JOIN <localtable> AS UL
ON UL.CLAIMID = WC.CLAIMID
WHERE UL.SOURCE = 'D2'
AND TYPE = 'WARRANTY'
I've also tried the following:
INSERT INTO <localtable>
SELECT *
FROM <linkedserver>.<remotedata base>.<sch ema>.<remo tetable>
WHERE CLAIMID IN ( SELECT CLAIMID
FROM <localtable>
WHERE SOURCE = 'D2'
AND TYPE = 'WARRANTY'
)
When I execute just the SELECT portion of the INSERT INTO statement from the stored procedure using TSQL I get 54K rows returned in 22 minutes.
I don't know if - or how - to use this when one table is local and the other is on the linked server and would like the Experts suggestions.
Here's the stored procedure:
INSERT INTO <localtable>
SELECT WC.*
FROM <linkedserver>.<remotedata
JOIN <localtable> AS UL
ON UL.CLAIMID = WC.CLAIMID
WHERE UL.SOURCE = 'D2'
AND TYPE = 'WARRANTY'
I've also tried the following:
INSERT INTO <localtable>
SELECT *
FROM <linkedserver>.<remotedata
WHERE CLAIMID IN ( SELECT CLAIMID
FROM <localtable>
WHERE SOURCE = 'D2'
AND TYPE = 'WARRANTY'
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Resolved using a different approach: SSIS / DTS
ASKER
WATiger: But I would have to send the ClaimIDs and use something like WHERE ClaimID IN (,,,,,) with a HUGH list. also: isn't this what EXEC (<sqlscript>) AT <linkedserver> is for?