Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL procedures doubt
PL/SQL procedures doubt [message #290079] Thu, 27 December 2007 05:54 Go to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Hi,

I am trying to populate datas from one (core) table to final table. I am getting datas for the core table everyday. The datas maybe new or the old one but updated.
I am using stored procedures to load the final tables.Whatever may be the data, old or new, it will be added to the core table.
When I am populating this core table data to final tables, I have to check the existance of the data.
If the data is already present, have to update. If not present, it will be added to the final table.

for example,
The very first day, all the datas (Let's hav the number of datas as 100) are new.
So every data will be added to final table.

The next day, consider the same data (same 100) is again loaded in the core table.
So the core table is having 200 datas.But the stored procedure will take the new ones to process.
Then it should update.

Here the problem I am facing is, The time taken to run the procedure is doubled everytime.
If the time taken is 30 mins in the first day, the next time it is taking 1 hr.
But the no of datas processed is same for both days.
And also, please tell me for insertion and updation will it take the same time to do?
Please advice.

Thanks in advance.

[Updated on: Thu, 27 December 2007 05:58]

Report message to a moderator

Re: PL/SQL procedures doubt [message #290080 is a reply to message #290079] Thu, 27 December 2007 06:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You have explained what you are doing but you have not told us how you are doing it. Can I assume you are using "Merge Statement to do the update/insert". So it is always better to post the query and more importantly a consolidated version. It will aid us to a greater extent in helping you out or pointing the right directions.

Regards

Raj
Re: PL/SQL procedures doubt [message #290082 is a reply to message #290080] Thu, 27 December 2007 06:07 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you Raj.
I am checking one unique column in the final table.
I am getting the core table datas in cursor.
By checking the existance of the core table data in the final,
I am inserting or updating.

My procedure is in the attachment.

Thanks in advance.
  • Attachment: Proc.sp
    (Size: 6.83KB, Downloaded 473 times)

[Updated on: Thu, 27 December 2007 06:08]

Report message to a moderator

Re: PL/SQL procedures doubt [message #290085 is a reply to message #290079] Thu, 27 December 2007 06:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


If i am not wrong , what you are trying to do is something illogical . Yopu might be trying to update the final table by taking full data from the core table. Performance may become worser as the volume of the data goes up . Instead you could,

1. Take data that is inserted newly in final table / updated after its actual insertion

2. Update the Final table Once insertion / upation is taken place in core table

3. Create trigger on Core table to insert /update the fiunal table on insertion / updation

But all depends on your system business rules .

Thumbs Up
Rajuvan.
Re: PL/SQL procedures doubt [message #290087 is a reply to message #290082] Thu, 27 December 2007 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Wow! All the errors that can be made are in this procedure:
- cursor loop row per row
- intermediate commits (commit in loop)
- when others without raise
- select max() to get an id
- ...
And all this may be done with only one MERGE statement

Do you allow me to use this procedure to show the worst PL/SQL practices?

Regards
Michel

Re: PL/SQL procedures doubt [message #290333 is a reply to message #290079] Fri, 28 December 2007 06:55 Go to previous message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hai,
you can create a before insert trigger and restrict the data insertion (if the data is already available)


Previous Topic: How select all days of specific month from dual ???
Next Topic: UTL_FILE
Goto Forum:
  


Current Time: Thu Feb 06 12:40:22 CST 2025