Link to home
Start Free TrialLog in
Avatar of Swamp_Thing
Swamp_ThingFlag for United States of America

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>.<remotedatabase>.<schema>.<remotetable> 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>.<remotedatabase>.<schema>.<remotetable>
             WHERE CLAIMID IN (  SELECT CLAIMID
                                         FROM <localtable>
                                        WHERE SOURCE = 'D2'
                                          AND TYPE = 'WARRANTY'
                                  )
SOLUTION
Avatar of Darrell Porter
Darrell Porter
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Swamp_Thing

ASKER

Addendum:   Run from T-SQL the procedure runs to completion; returning 54K rows in 22 minutes......

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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Resolved using a different approach: SSIS / DTS