| Sql return very slowly why ? [message #318558] |
Wed, 07 May 2008 03:12  |
mm_kanish05
Messages: 124
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
|
|
|
| Re: Sql return very slowly why ? [message #318591 is a reply to message #318558 ] |
Wed, 07 May 2008 04:30  |
Littlefoot
Messages: 5645
Registered: June 2005
Location: Croatia, Europe |
Senior Member |
|
|
|
As it is a static query, why wouldn't you create a VIEW and base your data block on this view?
|
|
|
|
|
| Re: Sql return very slowly why ? [message #318789 is a reply to message #318783 ] |
Thu, 08 May 2008 00:22 |
Littlefoot
Messages: 5645
Registered: June 2005
Location: Croatia, Europe |
Senior Member |
|
|
NOTHING changes in this query (at least, that's how you've written it). What I meant to way was:CREATE VIEW v_partnum AS
SELECT DISTINCT partnumber
FROM grinitem
WHERE unitid = 1
AND inspection_dt BETWEEN TO_DATE('01.04.2006', 'dd.mm.yyyy')
AND SYSDATE;
Now, open Forms Builder, use Data Block Wizard and base your block on the 'v_partnum' view. Fetch the rest of the columns in the POST-QUERY trigger or, even better, put it into the view; you'll need to join 'partmaster' and 'grinitem' tables.
|
|
|
| Re: Sql return very slowly why ? [message #319324 is a reply to message #318558 ] |
Fri, 09 May 2008 16:34 |
 |
psteve
Messages: 8
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]
|
|
|
]
Members
Search
Help
Register
Login
Home
