Home » SQL & PL/SQL » SQL & PL/SQL » performance problems
performance problems [message #251635] Sun, 15 July 2007 16:28 Go to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hello you all,

I am not a PL/SQL programmer,but in a phase of learning it.
I have written an application in PL/SQL code. I have tested the code and there is one function which takes a lot of processing time.

I have two tables. For every entry in one table A, I search the entry in table B and check if this entry has sub entries. If it has sub-entries I update table A.

This works fine, but takes to much time to process. And with tables which contains millions records, this is not an option. Can someone please check the functions and advise what I could do to gain performance?

The construction seems strange, but think of a couple of boxes. In one table I have Id’s belonging to every box, and in another table I have again these Id’s , but also information about other products in the same box.
If the boxid in table A exists in table B, I check if the box has more contents.

If this is not understandable, I’ll be happy to give another example.

Hope someone can help me out. I have checked out the bulk collection and the forall statement, but for me it seems that these new features have another purposes.

If it is possible to use these features, could someone please tell me how?


FUNCTION CheckEntry (p_id IN tableA.id%TYPE)
 RETURN BOOLEAN
 IS
 
/*here ofcourse declarations of the variables*/

BEGIN
   
      v_hasSubEntries := FALSE;
   

/* I am selecting the record which I need for the where clause, because on the attributes 
of this record I can determine rather there are more objects in the box
tableB has a lot of data, so this takes a long time for al the Id's.*/

      SELECT * into v_tableB
	     FROM tableB
	   WHERE tableB.id = p_id;

           SELECT count(*) INTO v_numberOfEntries
       FROM tableB tabB
	   WHERE tabB.xxx = v_tableB.xxx
            And tabB.yyy = v_tableB.yyy
  
       IF v_numberOfEntries >= 1
	    THEN 
		 v_hasSubEntries := TRUE;
	   END IF;
  
   RETURN v_v_hasSubEntries;
   --
   exception
   WHEN OTHERS
   THEN
--error/exception handling
END CheckEntry;


FUNCTION updateTableA 
 
 RETURN BOOLEAN
 IS

 
/*I get all the entries in one tableA and call the function CheckEntry 
if there are subentries for this Id. If yes, then I update tableA.
*/
 CURSOR c_ids IS
   SELECT tabA.ID 
  FROM tableA tabA;
   R_ids c_ids%TYPE;
 
 BEGIN


       FOR r_ids IN c_ids LOOP
	     
		 --function call of CheckEntry (returns TRUE or False)
             v_hasSubEntry := CheckEntry(r_ids.id);
		 IF v_hasSubEntry = TRUE
		  THEN
		  update tableA tabA
		   SET tabA.id = 'Y'
		   WHERE tabA = r_ids.id
		   
		  commit;
			
		 END IF;
		 
	   END LOOP;
		 
        
   RETURN TRUE;
    
   END updateTableA;





[Updated on: Sun, 15 July 2007 23:25] by Moderator

Report message to a moderator

Re: performance problems [message #251636 is a reply to message #251635] Sun, 15 July 2007 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Does your code actaully compile & run successfully?
Too bad you did not post actual descriptions of tables involved.
update tableA set tabA.id = 'Y'
where pid in ( select pid from tableB where tableB.id = tableA.id)

If a solution can be done using SQL, the solution should NOT be done using PL/SQL.
Please wrap your lines at 80 or 100 characters.

[Updated on: Sun, 15 July 2007 17:00] by Moderator

Report message to a moderator

Re: performance problems [message #251637 is a reply to message #251635] Sun, 15 July 2007 17:14 Go to previous messageGo to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
hi Anacedent,

I have re-written the code. The code works fine.
maybe it's easier to explain the issue with tables.



TableA
id	exist
	
1234	Y
1235	
1236	N
1237	
2345	
1239	
5467	
2387




TableB
Id 	shop	Zipcode	hnumber
1234	denver	1111GH	11
9876	denver	1111GH	11 --this one is in the same box as 1234
1000	houston	1254TS	123
1236	Mexico	2345LL	2345
2348	Germany	4563KK	1






So, I loop through tableA, and check in tableB the existence of the Id. Then I use the attributes of the Id, to check if there are more Id’s for those attributes. If yes, I update tableA.

I hope this makes it more understandable.

thanks in advance.


Re: performance problems [message #251644 is a reply to message #251635] Sun, 15 July 2007 18:52 Go to previous messageGo to next message
raj_23
Messages: 7
Registered: July 2007
Junior Member
try

update 
    tableA tabA
SET 
    tabA.id = 'Y'
WHERE 
    EXISTS 
    (
    SELECT 1 
    FROM
        tableB tabB
    WHERE
        tabA.XXX = tabB.XXX AND
        tabA.YYY = tabB.YYY
    )
    AND rownum <= 100000


You can limit the rowcount by some adding some kind of looping logic to update every 10000 or 100000 ..

raj Smile

[Updated on: Sun, 15 July 2007 18:54]

Report message to a moderator

Re: performance problems [message #251645 is a reply to message #251635] Sun, 15 July 2007 19:01 Go to previous messageGo to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
thanks Raj,

I am gonna work that out now, but the rownum idea is frightening me. Suppose that there are entries in tableB for a given id in tableA, but not in the selection of rownum 100000. Wouldn't it update tableA with 'N' where it should be update it with 'Y'?

Would it be faster to use bulk collections and the forall statement for this purpose? (if it is possible to use it for this purpose)

by the way..thanks for your reply!

regards
Re: performance problems [message #251649 is a reply to message #251644] Sun, 15 July 2007 20:48 Go to previous messageGo to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hi Raj,

Unfortenately this is not what I meant. I can't refer back to tableA, because I don't have the same attributes in tableA.

From tableA, I lookup for an entry in tableB, and then I need to find more occurences in tableB.

If you would like to check on the tables, you can see that tableA has an id with id number : 1234

This id number exists in tableB, so i should check the contents of the columns : shop Zipcode hnumber.
If i can find more columns in tableB with the same content then I need to update tableA with 'Y'.

the example with the boxes stinks Smile...so i try to give a better example

It's something like : id of member of a house is given in tableA. In tableB the address of that parent is given (which can be found with the id from tableA), and this address is again used to check in tableB if there are more people registered at the same address in tableB. If yes, tableA is updated.

hope this is a better example of what the two functions do and should do.

Regards and thanks in advance

Re: performance problems [message #251664 is a reply to message #251645] Mon, 16 July 2007 00:32 Go to previous messageGo to next message
raj_23
Messages: 7
Registered: July 2007
Junior Member
Hi there,

Probably can write 2 queries....
1. Dump into a temp table of everything in tableB with more than one count
2. Then in the update query use the temp table.

create table tempTableB
(
Id      NUMBER(10)
);

create index idx_id on tempTableB;


insert into tempTableB
select 
 Id
from
 tableB
group by
 Id
having
 count() > 1;


update 
    tableA tabA
SET 
    tabA.id = 'Y'
WHERE 
    EXISTS 
    (
    SELECT 1 
    FROM
        temptableB tabB
    WHERE
        tabA.XXX = tabB.XXX AND
        tabA.YYY = tabB.YYY
    )
    AND rownum <= 100000


For looping what i meant was use something like...

declare max_rows integer;
declare commit_point integer := 10000;
declare incr_rows integer := 0;
begin
  select 
    count(*)
  into
    max_rows;
  from
    tableA
end;
begin
    for i in 1..max_rows loop

      --update statement 
    update 
       tableA tabA
    SET 
       tabA.id = 'Y'
    WHERE 
      EXISTS 
      (
      SELECT 1 
      FROM
        temptableB tabB
      WHERE
        tabA.XXX = tabB.XXX AND
        tabA.YYY = tabB.YYY
      )
      AND rownum <= commit_point;
      
       incr_rows := incr_rows + commit_point;
       EXIT WHEN  incr_rows >= max_rows;
    end loop;
end;


Re: performance problems [message #251810 is a reply to message #251664] Mon, 16 July 2007 13:44 Go to previous message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hi Raj,

Yes indeed, I was thinking of creating a view.

1-create a view
2-insert all duplicate records
3-update tableB for every existence of the Id in tableA.

thanks for you replies, but it seems that this is the only way to accomplish this.

thanks anyway for your replies.

Previous Topic: can we create a trigger on one column
Next Topic: Trigger creation in a loop
Goto Forum:
  


Current Time: Fri Dec 09 19:13:27 CST 2016

Total time taken to generate the page: 0.09694 seconds