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).

Have you looked at the Oracle side at all? That's where you're inserting, that's where locks and constraints would most likely be. — Stephanie Page, Jun 28 '10 at 18:28
@Stephanie Haven't looked at the Oracle side. If there were a constraint wouldn't an error be thrown? I'll check deeper on that side but my problem is that I'm not as familiar with Oracle as I am SQL Server. — Clint Davis, Jun 28 '10 at 18:35
Create a table, with a single column, unique constraint. Start two session insert 1 from one session, then insert 1 from the second. The second will wait for the first to commit or rollback. There's only an error if one commits. But it can wait forever. But what POSSIBLE indeterminate waits could you encounter READING from SQL Server? Yes, there's probably 1 thread but but why watch the side with ZERO chance of blocking? Only because you're most familiar? That's like staring at the empty fuel gauge in the car and wondering why there's no gas coming out of the pump. — Stephanie Page, Jun 29 '10 at 13:32
Also keep in mind that many people fail to compare times correctly. Query tools don't bring back all the results at once. There's a big difference in producing the first row as opposed to the last row. With NL Joins, I can get the first row very fast, but the last row could take forever. So you "Query" may "Return" fast but it's not "Done". — Stephanie Page, Jun 29 '10 at 13:36
I don't usually have a problem on the Oracle side, 99% of the time I got something wrong on the SQL Server side. I found out by looking at locks that I had a non-committed transaction. I committed the action (delete) and then another error popped up about conflicting primary id. That error I can handle. — Clint Davis, Jun 29 '10 at 14:39

score 0 · Answer 1 · answered Jun 28 '10 at 18:38

0

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.

answered Jun 28 '10 at 18:38

d_schnell

614
4
5

Avoid large transaction logs on which side? Oracle? That's not really a concern. Each insert will contain the same amount of transaction log regardless of how frequently you commit. Are you talking about Rollback Space or Undo Space? Bulk batch? This is Bulk Batch (Seems redundant). He's doing all the rows, all at once... Batch; as opposed to one at a time... Transactional. – Stephanie Page Jun 29 '10 at 13:40
I was trying to avoid a temp table but that's what I ended up doing anyway. I don't understand what a bulk batch process is, I thought that is what I was doing. – Clint Davis Jun 29 '10 at 14:34
Linked servers have different problems. One thing I always try to avoid is moving large chunks of data accross the wire. And the best way doing this is to find a good partition key to divide the data into smaller chunks. I'm talk about 25k to 50k rows and more. I bet if he's doing so the import will succeed. – d_schnell Jun 29 '10 at 18:10
Long running transactions prevent the database server from discarding archived log entries. – d_schnell Jun 29 '10 at 18:19

score 0 · Answer 2 · answered Jun 28 '10 at 20:21

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.

When I just ran the select the data looked right. No triggers on the table. I'm sure the network is slowing me down but like I said I have other similar jobs that run fine. — Clint Davis, Jun 29 '10 at 14:35

Linked Server Query Runs But Doesn't Finish?

2 Answers2