Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL update loop takes a looooong time
PLSQL update loop takes a looooong time [message #184788] Fri, 28 July 2006 01:04 Go to next message
tansiakl
Messages: 3
Registered: July 2006
Junior Member
Hello all,

I have the following PLSQL. THe cursor select takes less than a minute but inside the loop, the update takes a long time. About 100 records per minute. (5000 records update takes about 40 mins.) Most of the loop will update the record rather than inserting. And the index for the TEMP_DIM_PACKAGE is the package_name.

Thanks

PROCEDURE PRO_LOAD_PACKAGE(int_Output IN OUT INTEGER) IS
int_Insert_Count PLS_INTEGER;
int_Update_Count PLS_INTEGER;
nn_Row MST_PACKAGE%ROWTYPE;

CURSOR cur_get_from_MST IS
SELECT * FROM MST_PACKAGE
;

BEGIN

DBMS_OUTPUT.Put_Line ('(PRO_LOAD_PACKAGE) Time Start := ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

int_Insert_Count := 0;
int_Update_Count := 0;

FOR nn_Row IN cur_get_from_MST LOOP

UPDATE TEMP_DIM_PACKAGE SET
--ID = nn_Row.Ifpa_Id,
PACKAGE_NAME = nn_Row.Ifpa_Package_Name,
PACKAGE_GROUP = nn_Row.Ifpa_Package_Group,
PACKAGE_CLASS = nn_Row.Ifpa_Package_Class,
PACKAGE_FAMILY = nn_Row.Ifpa_Package_Family,
PACKAGE_TECHNOLOGY = nn_Row.Ifpa_Package_Technology,
TIME_STAMP = nn_Row.Ifpa_Time_Stamp,
ADM_DATA_STATUS = nn_Row.Ifpa_Adm_Data_Status,
PACKAGE_NAME_DESCR = nn_Row.Ifpa_Package_Name_Descr,
PACKAGE_GROUP_DESCR = nn_Row.Ifpa_Package_Group_Descr,
PACKAGE_CLASS_DESCR = nn_Row.Ifpa_Package_Class_Descr,
PACKAGE_FAMILY_DESCR = nn_Row.Ifpa_Package_Family_Descr,
PACKAGE_TECHNOLOGY_DESCR = nn_Row.Ifpa_Package_Technology_Descr
WHERE
PACKAGE_NAME = nn_Row.Ifpa_Package_Name
;

IF SQL%NOTFOUND THEN
INSERT INTO TEMP_DIM_PACKAGE
(ID, PACKAGE_NAME, PACKAGE_GROUP, PACKAGE_CLASS, PACKAGE_FAMILY,
PACKAGE_TECHNOLOGY, TIME_STAMP, ADM_DATA_STATUS, PACKAGE_NAME_DESCR, PACKAGE_GROUP_DESCR,
PACKAGE_CLASS_DESCR, PACKAGE_FAMILY_DESCR, PACKAGE_TECHNOLOGY_DESCR)
VALUES
(nn_Row.Ifpa_Id, nn_Row.Ifpa_Package_Name, nn_Row.Ifpa_Package_Group, nn_Row.Ifpa_Package_Class, nn_Row.Ifpa_Package_Family,
nn_Row.Ifpa_Package_Technology, nn_Row.Ifpa_Time_Stamp, nn_Row.Ifpa_Adm_Data_Status, nn_Row.Ifpa_Package_Name_Descr, nn_Row.Ifpa_Package_Group_Descr,
nn_Row.Ifpa_Package_Class_Descr, nn_Row.Ifpa_Package_Family_Descr, nn_Row.Ifpa_Package_Technology_Descr);
int_Insert_Count := int_Insert_Count + 1;
ELSE
int_Update_Count := int_Update_Count + 1;
END IF;

END LOOP;
COMMIT;
int_Output := int_Output + 0;

DBMS_OUTPUT.Put_Line ('(PRO_LOAD_PACKAGE) Inserted Record Count := ' || int_Insert_Count);
DBMS_OUTPUT.Put_Line ('(PRO_LOAD_PACKAGE) Updated Record Count := ' || int_Update_Count);

DBMS_OUTPUT.Put_Line ('(PRO_LOAD_PACKAGE) Time End := ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

-- Exception Handling
EXCEPTION
WHEN OTHERS THEN
int_Output := int_Output + 100;
ROLLBACK;
dbms_output.put_line (sqlerrm);
END PRO_LOAD_PACKAGE;
Re: PLSQL update loop takes a looooong time [message #184794 is a reply to message #184788] Fri, 28 July 2006 01:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Check whether there is Index on PACKAGE_NAME of TEMP_DIM_PACKAGE .

OTHER WISE Go for straight SQL .. with MERGE Statement .

Just google "MERGE + Oracle "

Thumbs Up
Rajuvan.
Re: PLSQL update loop takes a looooong time [message #185110 is a reply to message #184794] Mon, 31 July 2006 01:11 Go to previous messageGo to next message
tansiakl
Messages: 3
Registered: July 2006
Junior Member
Hello Rajuvan,

Cant use merge bcos the oracle version i had is 8i.

There is already a index on Package_Name. what else can i do to make the procedure faster.

The problem is that when I use datastage to update the table, it is very fast, by when I switch to PLSQL the loop is running a long time.

Please comment.

Thanks

Bernard
Re: PLSQL update loop takes a looooong time [message #185137 is a reply to message #185110] Mon, 31 July 2006 02:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Why can't you use something like ...

UPDATE TEMP_DIM_PACKAGE dim
SET  (PACKAGE_GROUP ,
      PACKAGE_CLASS ,
      PACKAGE_FAMILY ,
      PACKAGE_TECHNOLOGY ,
      TIME_STAMP ,
      ADM_DATA_STATUS ,
      PACKAGE_NAME_DESCR ,
      PACKAGE_GROUP_DESCR ,
      PACKAGE_CLASS_DESCR ,
      PACKAGE_FAMILY_DESCR ,
      PACKAGE_TECHNOLOGY_DESCR)  = (SELECT IFA_PACKAGE_GROUP ,
                                           IFA_PACKAGE_CLASS ,
                                           IFA_PACKAGE_FAMILY ,
                                           IFA_PACKAGE_TECHNOLOGY ,
                                           IFA_TIME_STAMP ,
                                           IFA_ADM_DATA_STATUS ,
                                           IFA_PACKAGE_NAME_DESCR ,
                                           IFA_PACKAGE_GROUP_DESCR ,
                                           IFA_PACKAGE_CLASS_DESCR ,
                                           IFA_PACKAGE_FAMILY_DESCR ,
                                           IFA_PACKAGE_TECHNOLOGY_DESCR
                                     FROM  MST_PACKAGE mst
                                     WHERE mst.IFA_AGE_NAME = dim.PACKAGE_NAME )
Where exists (select Null 
              from MST_PACKAGE mst 
              where mst.PACKAGE_NAME = dim.PACKAGE_NAME ) ;


INSERT INTO TEMP_DIM_PACKAGE (PACKAGE_NAME ,
                             PACKAGE_GROUP ,
                             PACKAGE_CLASS ,
                             PACKAGE_FAMILY ,
                             PACKAGE_TECHNOLOGY ,
                             TIME_STAMP ,
                             ADM_DATA_STATUS ,
                             PACKAGE_NAME_DESCR ,
                             PACKAGE_GROUP_DESCR ,
                             PACKAGE_CLASS_DESCR ,
                             PACKAGE_FAMILY_DESCR ,
                             PACKAGE_TECHNOLOGY_DESCR)
Select  IFA_PACKAGE_NAME ,
        IFA_PACKAGE_GROUP ,
        IFA_PACKAGE_CLASS ,
        IFA_PACKAGE_FAMILY ,
        IFA_PACKAGE_TECHNOLOGY ,
        IFA_TIME_STAMP ,
        IFA_ADM_DATA_STATUS ,
        IFA_PACKAGE_NAME_DESCR ,
        IFA_PACKAGE_GROUP_DESCR ,
        IFA_PACKAGE_CLASS_DESCR ,
        IFA_PACKAGE_FAMILY_DESCR ,
        IFA_PACKAGE_TECHNOLOGY_DESCR
FROM   MST_PACKAGE mst
where NOT Exists (select Null 
                  from  TEMP_DIM_PACKAGE dim
                  where dim.PACKAGE_NAME  = mst.IFA_PACKAGE_NAME );


Thumbs Up
Rajuvan.

Re: PLSQL update loop takes a looooong time [message #185454 is a reply to message #185137] Tue, 01 August 2006 17:52 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
You could update a join view (which can be an inline view) assuming an appropriate unique contraint is in place on the parent table.

Or staying with the old-school loop, I would at least make the cursor an outer join between the two tables. Then I would know within the loop whether I had an insert or an update to do.

Bulk collect and FORALL could help here as well.

btw just noticed

UPDATE temp_dim_package
SET    package_name = nn_row.ifpa_package_name,
...
WHERE  package_name = nn_row.ifpa_package_name

Seems like package_name already has the value you are updating it to.

[Updated on: Tue, 01 August 2006 17:56]

Report message to a moderator

Previous Topic: WAT IS BASIC USE OF
Next Topic: improve group by query in table with partitions
Goto Forum:
  


Current Time: Wed Dec 07 16:46:07 CST 2016

Total time taken to generate the page: 0.14095 seconds