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  |
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 #185110 is a reply to message #184794] |
Mon, 31 July 2006 01:11  |
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 |
 |
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 );
Rajuvan.
|
|
|
|
| Re: PLSQL update loop takes a looooong time [message #185454 is a reply to message #185137] |
Tue, 01 August 2006 17:52 |
William Robertson
Messages: 1643
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 |
|
|
|
Goto Forum:
Current Time: Tue Dec 03 06:14:15 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
