PLSQL procedure takes very long to execute

Question

I have created the below procedure which ran just fine before I added the bold code. Now it's inserting 100 records at a time. It's been more than a hour and it has inserted only 4000 records. There are 2 million records to be inserted. Is there any way I can fasten the execution?

create or replace PROCEDURE LOAD_ADDRESS_PROCEDURE AS 

CNTY varchar2(100);

ST varchar2(100); 

createdby_value varchar2(50);

**defaultAddress_value varchar2(1);**

CURSOR C1 is 

SELECT ADDRESS, CITY, STATE, ZIP, COUNTRY, seasonal_start_day, seasonal_start_month, seasonal_end_day, seasonal_end_month, key_address_id, key_account_id, BAD_ADDRESS,
CREATED, CREATED_BY, address_type,

(select siebel_country_name from STATE_AND_COUNTRY_VALUES_LIST where s.COUNTRY IS NULL and s.state = siebel_state_abbreviation) newCNTY,

(select SF_COUNTRY_ABBREVIATION FROM STAGE_COUNTRY WHERE SIEBEL_COUNTRY = s.Country) newCountry,

(select SF_COUNTRY_ABBREVIATION FROM STAGE_COUNTRY 
WHERE SIEBEL_COUNTRY = (select siebel_country_name from STATE_AND_COUNTRY_VALUES_LIST where s.COUNTRY IS NULL and s.state = siebel_state_abbreviation)) newCountry1,

(SELECT SIEBEL_LOGIN from STAGE_USER where KEY_USER_ID = s.CREATED_BY) SiebelLogin,

(SELECT SALESFORCE_USER from STAGE_USER where KEY_USER_ID = s.CREATED_BY) SFUSER,

(SELECT KEY_USER_ID from STAGE_USER where KEY_USER_ID = s.CREATED_BY) KeyUserID,

**(SELECT KEY_PRIMARY_ADDRESS_ID FROM STAGE_ACCOUNT WHERE KEY_PRIMARY_ADDRESS_ID = s.KEY_ADDRESS_ID) defaultAddress**

FROM STAGE_ADDRESS s;

BEGIN

FOR i IN C1 LOOP

ST := i.state;

CNTY := i.country;

/*if(i.newCountry = i.newCNTY) then*/

/*if (i.country  is null) then

CNTY := i.newCountry1;

end if;

comment end*/

if (i.state = 'NA') then

ST := NULL;

end if;

/*end if;*/

/*

if(CNTY != i.newCountry) then

CNTY := i.newCountry;

end if;

if(i.newCountry is null) then

CNTY := NULL;

end if;

comment end*/

IF(CNTY IS NULL) THEN
    CNTY := i.newCountry1;
    ELSE
    CNTY := i.newCountry;
END IF;

IF(i.CREATED_BY = i.KeyUserID AND i.SFUSER = 'MIGRATIONUSER') THEN
createdby_value := 'MIGRATIONUSER';
ELSE
createdby_value := i.KeyUserID;
END IF;

**IF(i.defaultAddress IS NOT NULL) THEN
defaultAddress_value := 'Y';
ELSE
defaultAddress_value := 'N';
END IF;**

INSERT INTO LOAD_ADDRESS(NPSP__MAILINGSTREET__C, NPSP__MAILINGCITY__C, NPSP__MAILINGSTATE__C, NPSP__MAILINGPOSTALCODE__C, NPSP__MAILINGCOUNTRY__C, NPSP__SEASONAL_START_DAY__C,
NPSP__SEASONAL_START_MONTH__C, NPSP__SEASONAL_END_DAY__C, NPSP__SEASONAL_END_MONTH__C, ERP_EXTERNAL_ID_C, NPSP__HOUSEHOLD_ACCOUNT__C, ADDRESS_STATUS_OVERRIDE, CREATED, 
CREATED_BY, NPSP_ADDRESS_TYPE_C, **NPSP__DEFAULT_ADDRESS__C**) VALUES 
(i.ADDRESS, REGEXP_REPLACE(i.CITY, '|||||', ' '), ST, i.ZIP, CNTY, i.seasonal_start_day, i.seasonal_start_month, i.seasonal_end_day, i.seasonal_end_month, i.key_address_id, i.key_account_id, 
DECODE(i.BAD_ADDRESS,'Y','1','N','0'), i.CREATED, createdby_value, i.address_type, **defaultAddress_value**);

COMMIT;

END LOOP;

END LOAD_ADDRESS_PROCEDURE;

Boneist · Accepted Answer

You could rewrite your procedure to do all the logic in a single insert statement, something like:

INSERT INTO load_address
  (npsp__mailingstreet__c,
   npsp__mailingcity__c,
   npsp__mailingstate__c,
   npsp__mailingpostalcode__c,
   npsp__mailingcountry__c,
   npsp__seasonal_start_day__c,
   npsp__seasonal_start_month__c,
   npsp__seasonal_end_day__c,
   npsp__seasonal_end_month__c,
   erp_external_id_c,
   npsp__household_account__c,
   address_status_override,
   created,
   created_by,
   npsp_address_type_c,
   npsp__default_address__c)
  SELECT address,
         regexp_replace(i.city, '|||||', ' '),
         CASE
           WHEN state = 'NA' THEN
            NULL
           ELSE
             state
         END state,
         zip,
         CASE
           WHEN country IS NULL THEN
            newcountry1
           ELSE
            newcountry
         END country,
         seasonal_start_day,
         seasonal_start_month,
         seasonal_end_day,
         seasonal_end_month,
         key_address_id,
         key_account_id,
         DECODE(bad_address, 'Y', '1', 'N', '0'),
         created,
         CASE
           WHEN created_by = keyuserid
                AND sfuser = 'MIGRATIONUSER' THEN
            sfuser
           ELSE
            keyuserid
         END createdby_value,
         address_type,
         CASE
           WHEN default_address IS NOT NULL THEN
            'Y'
           ELSE
            'N'
         END defaultaddress_value
  FROM   (SELECT address,
                 city,
                 state,
                 zip,
                 country,
                 seasonal_start_day,
                 seasonal_start_month,
                 seasonal_end_day,
                 seasonal_end_month,
                 key_address_id,
                 key_account_id,
                 bad_address,
                 created,
                 created_by,
                 address_type,

                 (SELECT siebel_country_name
                  FROM   state_and_country_values_list
                  WHERE  s.country IS NULL
                  AND    s.state = siebel_state_abbreviation) newcnty,

                 (SELECT sf_country_abbreviation
                  FROM   stage_country
                  WHERE  siebel_country = s.country) newcountry,

                 (SELECT sf_country_abbreviation
                  FROM   stage_country
                  WHERE  siebel_country = (SELECT siebel_country_name
                                           FROM   state_and_country_values_list
                                           WHERE  s.country IS NULL
                                           AND    s.state = siebel_state_abbreviation)) newcountry1,

                 su.siebel_login siebellogin,
                 su.salesforce_user sfuser,
                 su.key_user_id keyuserid,

                 (SELECT key_primary_address_id
                  FROM   stage_account
                  WHERE  key_primary_address_id = s.key_address_id) defaultaddress
          FROM   stage_address s
                 LEFT OUTER JOIN stage_user su ON s.created_by = su.key_user_id);

I have moved your scalar subqueries on status_user to an outer join, since joining is likely to be more efficient than querying the same table 3 times. You could probably do something similar with the other scalar subqueries (i.e. combining them into a couple of joins or something) but I've left that for you to play with.

PLSQL procedure takes very long to execute

Answers (2)

Related Questions