Home » Developer & Programmer » Forms » Query running slow in Form but fast in DB
Query running slow in Form but fast in DB [message #635688] Mon, 06 April 2015 13:12 Go to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Hi All,

I have a query which when I run outside runs within minutes but the same query when put inside a form runs very long. can somebody please suggest some ways which can be used to speed it up inside form also?

here is the query I am using:
SELECT dept,supp,class,style,item_desc,vpn,color FROM  (SELECT DISTINCT /*+parallel(4) ordered */ im.dept, isp.supplier supp, im.class, im.item_parent style, (SELECT im1.item_desc
                               FROM rmsapps.item_master im1
                              WHERE im1.item = im.item_parent) item_desc, isp.vpn, im.diff_2 color FROM item_supplier isp, item_master im, (SELECT NVL(a.item,'0') item
                                FROM (SELECT DISTINCT /*+ use_hash */ il.item
                                        FROM item_loc il,
                                             repl_eligibility bre
                                       WHERE bre.status = 'A'
                                         AND il.status = 'A'
                                                     AND il.item = bre.item
                                                     AND il.loc = bre.location
                                                     AND EXISTS (SELECT im2.item
                                                                   FROM item_master im2
                                                                  WHERE im2.item = il.item
                                                                    AND im2.forecast_ind = 'Y'
                                                                    AND im2.item_level = 2
                                                                    AND im2.pack_ind = 'N' AND im2.dept = 292) AND EXISTS(SELECT NULL /*+ index */
                                                                                                                          FROM item_loc_hist ilh
                                                                                                                          WHERE (eow_date) < ADD_MONTHS(SYSDATE,-12)
                                                                                                                          AND ilh.item=il.item AND il.loc=ilh.loc )) a ) b  WHERE im.item = isp.item  AND im.item = b.item 
 ORDER BY dept, class, supp, style, color);



Re: Query running slow in Form but fast in DB [message #635690 is a reply to message #635688] Mon, 06 April 2015 13:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As this SELECT doesn't use any form items, create a view and - in your form - select from that view.
Re: Query running slow in Form but fast in DB [message #635695 is a reply to message #635690] Tue, 07 April 2015 00:34 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Sorry It does. It's basically a dynamic SQL which forms by taking inouts from form items. So yeah it has form elements. I was thinking of making a function which will take inputs(form elements) and then it will return a Collection . Which I can then use in this way.

SELECT * from TABLE(function_sample(p1,p2,p3,p4....)) ;

Can I use collection inside a form?
Re: Query running slow in Form but fast in DB [message #635696 is a reply to message #635695] Tue, 07 April 2015 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know, I never did it (if you try, you'll tell me).

Though, I used a procedure as a data block's source. So, if you can't make it work the way you suggested, try a procedure. Form items' values will be procedure's IN parameters. Here is a short walkthrough, if you'll need it.
Re: Query running slow in Form but fast in DB [message #635697 is a reply to message #635696] Tue, 07 April 2015 01:01 Go to previous message
Asfakul
Messages: 43
Registered: July 2014
Member
Thanks , I will try it out and let you know.
Previous Topic: FRM-40733 pl/sql built-in SET_GROUP_CHAR_CELL failed
Next Topic: FORMS
Goto Forum:
  


Current Time: Thu Mar 28 23:45:09 CDT 2024