Home » Developer & Programmer » Forms » Sql return very slowly why ? (form 6i,oracle 9i,widows xp)
Sql return very slowly why ? [message #318558] Wed, 07 May 2008 03:12 Go to next message
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_pressed
declare
  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 #318591 is a reply to message #318558] Wed, 07 May 2008 04:30 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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 #318762 is a reply to message #318591] Wed, 07 May 2008 21:32 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I do not like this approach of populating a block manually.
My feelings about it.
http://www.orafaq.com/forum/m/134237/67467/?srch=populate+block+cursor+create_record#msg_134237
Another one of my rants.
http://www.orafaq.com/forum/mv/msg/74947/213213/67467/#msg_213213

If you used a block based on 'grinitem' with a 'post-query' to get the ':partname' (please specify the 'block' as well as the 'item') you would not have to fetch ALL the records.

Also, "inspection_dt between '01-apr-2006' and sysdate" should be "inspection_dt between to_date('01-apr-2006','dd-mon-yyyy') and sysdate" because the nls_date may be different between your Developer Server and your Application Server and your Database Server.

Now, do you have an index on 'unitid' or 'inspection_dt'? Is 'unitid' numeric? Please index the date field.

What about 'partnumber' in 'partmaster'? Is it indexed? It should be.

David
Re: Sql return very slowly why ? [message #318783 is a reply to message #318558] Wed, 07 May 2008 23:52 Go to previous messageGo to next message
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
Senior Member

hi

little, do u feel is a static query


Select distinct partnumber
                From grinitem
               Where unitid = 1
                 And inspection_dt between '01-apr-2006' and sysdate



wbr
kanish
Re: Sql return very slowly why ? [message #318789 is a reply to message #318783] Thu, 08 May 2008 00:22 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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 Go to previous message
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

Previous Topic: working Directory Of 10g DS
Next Topic: Converting Forms 6i to 10g
Goto Forum:
  


Current Time: Sun Feb 09 08:29:34 CST 2025