Linked Server Query Runs But Doesn't Finish?

Question

June 29, 2010 - I had an un-committed action from a previous delete statement. I committed the action and I got another error about conflicting primary id's. I can fix that. So morale of the story, commit your actions.

Original Question -

I'm trying to run this query:

with spd_data as (
select *  
from openquery(IRPROD,'select * from budget_user.spd_data where fiscal_year = 2010')
) 

insert into [IRPROD]..[BUDGET_USER].[SPD_DATA_BUD] 
  (REC_ID, FISCAL_YEAR, ENTITY_CODE, DIVISION_CODE, DEPTID, POSITION_NBR, EMPLID, 
   spd_data.NAME, JOB_CODE, PAY_GROUP_CODE, FUND_CODE, FUND_SOURCE, CLASS_CODE,
   PROGRAM_CODE, FUNCTION_CODE, PROJECT_ID, ACCOUNT_CODE, SPD_ENC_AMT, SPD_EXP_AMT, 
   SPD_FB_ENC_AMT, SPD_FB_EXP_AMT, SPD_TUIT_ENC_AMT, SPD_TUIT_EXP_AMT, 
   spd_data.RUNDATE, HOME_DEPTID, BUD_ORIG_AMT, BUD_APPR_AMT)
SELECT REC_ID, FISCAL_YEAR, ENTITY_CODE, DIVISION_CODE, DEPTID, POSITION_NBR, EMPLID,
       spd_data.NAME, JOB_CODE, PAY_GROUP_CODE, FUND_CODE, FUND_SOURCE, CLASS_CODE, 
       PROGRAM_CODE, FUNCTION_CODE, PROJECT_ID, ACCOUNT_CODE, SPD_ENC_AMT, SPD_EXP_AMT,
       SPD_FB_ENC_AMT, SPD_FB_EXP_AMT, SPD_TUIT_ENC_AMT, SPD_TUIT_EXP_AMT, 
       spd_data.RUNDATE, HOME_DEPTID, lngOrig_amt, lngAppr_amt
  from spd_data
left join Budgets.dbo.tblAllPosDep on project_id = projid 
                                  and job_code = jcc and position_nbr = psno
                                  and emplid = empid
where  OrgProjTest = 'EQUAL';

Basically I'm selecting a table from IRPROD (an oracle db), joining it with a local table, and inserting the results back on IRPROD.

The problem I'm having is that while the query runs, it never stops. I've let it run for an hour and it keeps going until I cancel it. I can see on a bandwidth monitor on the SQL Server data going in and out. Also, if I just run the select part of the query it returns the results in 4 seconds.

Any ideas why it's not finishing? I've got other queryies setup in a similar manner and do not have any problems (granted those insert from local tables and not a remote table).

Answer 1

You didn't included any volume metrics. But I would recommend to use a temporary table to gather the results.

Then you should try to insert the first couple of rows. If this succeeds you'll have a strong indicator that everything is fine.

Try to break down each insert task by project_id or emplid to avoid large transactions logs.

You should also think about crafting a bulk batch process.

Answer 2

If you run just the select without the insert, how many records are returned? Does the data look right or are there multiple records due to the join?

Are there triggers on the table you are inserting into? If you are returning many records and triggers are on the table that are designed to run row-byrow this could be slowing things down. You are also sending to another server, so the network pipeline could be what is slowing you down. Maybe it would be better to send the budget data to the Oracle server and do the insert from there rather than from the SQL Server.