Insert into Oracle/select from SQL Server -- poor performance.

• DarylAndDaryl

  Default port

  Points: 1491

  More actions

  June 6, 2006 at 12:17 pm

  #112502

  I have Windows 2000 SP4 and SQL Server SP3. I have a linked server from SQL Server to Oracle 9i on UNIX 11i. The following is my statement:

  Short version:

  INSERT INTO MISPRDWH_LINK..DDPCS.CRM_CALLACTIVITY_MGT

  SELECT * FROM crm_callactivity_mgt

  WHERE history_id < 1000001
  AND history_id < 2000001 Long version:
  INSERT INTO MISPRDWH_LINK..DDPCS.CRM_CALLACTIVITY_MGT
  ( SSN
  ,HISTORY_ID
  ,USER_LOGIN
  ,SUB_LAST_NAME
  ,ASSIGNED_TO_USER_LOGIN
  ,CALLER_TYPE
  ,CONTACT_DATE
  ,CONTACT_TYPE
  ,CALL_RESOLUTION_CODE
  ,CALL_STATUS
  ,SHORT_DESC
  ,LONG_DESC
  ,CARRIER
  ,GROUP_NUM
  ,PROGRAM_TYPE
  ,SUB_GROUP_ID
  ,EMAIL_IND
  ,ALERT_IND
  ,EMAIL_ADDRESS
  ,ASSIGNED_TO_DATE
  ,CALL_DURATION
  ,BUG_CAT_DESC
  ,EXPECTED_COMPLETE_DATE
  ,CREATION_USER_LOGIN
  ,CALL_CLOSED_DATE
  ,DCN_DCN
  ,CREATION_DATE
  ,DCN_TYPE
  ,LAST_UPD_USER_LOGIN
  ,LAST_UPD_DATE
  ,ALERT_ID)
  SELECT ssn
  ,history_id
  ,user_login
  ,sub_last_name
  ,assigned_to_user_login
  ,caller_type
  ,contact_date
  ,contact_type
  ,call_resolution_code
  ,call_status
  ,short_desc
  ,long_desc
  ,carrier
  ,group_num
  ,program_type
  ,sub_group_id
  ,email_ind
  ,alert_ind
  ,email_address
  ,assigned_to_date
  ,call_duration
  ,bug_cat_desc
  ,expected_complete_date
  ,creation_user_login
  ,call_closed_date
  ,dcn_dcn
  ,creation_date
  ,dcn_type
  ,last_upd_user_login
  ,last_upd_date
  ,alert_id
  FROM crm_callactivity_mgt
  WHERE history_id < 1000001
  AND history_id < 2000001 The table in the INSERT INTO is the Oracle table the table in the SELECT is the SQL Server table. The SELECT will run in 2 minutes. I kill the INSERT INTO after running for 40 minutes. The explain shows small costs except Table Spool/Eager Spool, which has a cost of 98%. I think this Table Spool/Eager Spool is the problem but I am not finding very many discussion about it. I do have a clustered index on history_id and according to the explain plan, it does a clustered index seek. Does anyone know what is Table Spool/Eager Spool and is this what is giving my code extremely poor performance? Is there anything I can do to increase performance?

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  June 6, 2006 at 1:08 pm

  #642122

  Here's the BOL for Eager Spool:

  http://msdn2.microsoft.com/en-us/library/ms190435.aspx

  "The Eager Spool operator builds its spool file in an "eager" manner: when the spool's parent operator asks for the first row, the spool operator consumes all rows from its input operator and stores them in the spool. "

  "WHERE history_id < 1000001

  AND history_id < 2000001"

  I'm assuming this is a typo...?