Sql return very slowly why ? [message #318558] |
Wed, 07 May 2008 03:12  |
mm_kanish05
Messages: 493 Registered: January 2007 Location: Chennai
|
Senior Member |

|
|
Hi
Im running a sql which return 4840 record only eventhoug it takes 2 minutes. CAn i know why;
Im fetching row using cursor on when_button_presseddeclare
Cursor Recntpart is
(Select distinct partnumber
From grinitem
Where unitid = 1
And inspection_dt between '01-apr-2006' and sysdate);
Rpart Recntpart%rowtype;
Begin
Open Recntpart;
Go_item ('partnumber');
First_Record;
Loop
Fetch Recntpart
into Rpart;
Exit When Recntpart%NotFound;
:partnumber := Rpart.Partnumber;
Select partname1
into :partname
from partmaster
Where partnumber = Rpart.Partnumber;
Next_Record;
End Loop;
Close Recntpart;
End;
Advance Thanks
kanish
Upd Mod: Reformat for clarity.
[Updated on: Wed, 07 May 2008 21:16] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Sql return very slowly why ? [message #319324 is a reply to message #318558] |
Fri, 09 May 2008 16:34  |
 |
psteve
Messages: 9 Registered: March 2005 Location: New England
|
Junior Member |
|
|
Try this
go_block('my_block');
for x in (
select distinct
a.partnumber,
b.partname
from
grinditem a,
partmaster b
where
a.unitid = 1 and
a.inspection_dt between '01-apr'2006' and sysdate and
a.partnumber = b.partnumber) -- depending on your db, outer join might be required
loop
insert_record;
:my_block.partnumber := x.partnumber;
:my_block.partname := x.partname;
end loop;
This will be faster because it asks the DB to perform one query.
The original code asks for a query every loop iteration (i.e. more than 4000).
Also notice how much simpler the for loop is.
[Updated on: Fri, 09 May 2008 16:42] Report message to a moderator
|
|
|