Home » SQL & PL/SQL » SQL & PL/SQL » Lexical Parameter Slow down query (Oracle 10g)
Lexical Parameter Slow down query [message #443500] Tue, 16 February 2010 01:44 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear All my following query is running fine.
SELECT   pd,
         vr_date,
         x.vr_no,
         vr_sn,
         cac,
         ac,
         nar,
         dr_amount,
         cr_amount,
         abbr,
         unit,
         y.year,
         y.due_Date
  FROM   account.trans x, account.tr_details y, account.vr_type z
 WHERE       x.vr_no = y.vr_no
         AND x.year = y.year
         AND x.vr_type = z.vr_type
         AND pd BETWEEN 7 and 8
         and Unit in(29,34,35,36,37,38,44,45,60,70,71)
         AND y.year BETWEEN 2010 and 2010


but problem is that when above query is used or oracle reports then and lexical parameter is used then i slows down more than 100 times
SELECT   pd,
         vr_date,
         x.vr_no,
         vr_sn,
         cac,
         ac,
         nar,
         dr_amount,
         cr_amount,
         abbr,
         unit,
         y.year,
         y.due_Date
  FROM   account.trans x, account.tr_details y, account.vr_type z
 WHERE       x.vr_no = y.vr_no
         AND x.year = y.year
         AND x.vr_type = z.vr_type
         AND pd BETWEEN 7 and 8
         &P_Unt_Cls
         AND y.year BETWEEN 2010 and 2010



above query is copy of top query but differnce is that &P_Unt_Cls lexical parameter is used instead of and Unit in(29,34,35,36,37,38,44,45,60,70,71), I am unable to understand why it slow down query.

Thanks in advance

Regards.
Muhammd Asif.
Re: Lexical Parameter Slow down query [message #443529 is a reply to message #443500] Tue, 16 February 2010 02:48 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle
Have you view this problem
Re: Lexical Parameter Slow down query [message #443543 is a reply to message #443529] Tue, 16 February 2010 03:15 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you create a lexical parameter?
What is "unit" column's datatype?
Re: Lexical Parameter Slow down query [message #443546 is a reply to message #443500] Tue, 16 February 2010 03:22 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear,
Unit datatype is number, In Before Report trigger i have written
Unit in((29,34,35,36,37,38,44,45,60,70,71)


I need this because difference user need report for different units. The select unit type in parameter form then i identify the prameter value in before report trigger and assign value to parameter for query.

Thanks in advance.

Regards.
Asif.
Re: Lexical Parameter Slow down query [message #443551 is a reply to message #443546] Tue, 16 February 2010 03:48 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You set the lexical parameter value in Before Report trigger? Did you try to do that in After Parameter Form one, instead?


What happens if you create additional table that contains units for different user types? Then you'd only have user parameter (and no lexical parameter), while the query would look like
select ...
from ...
where ...
  -- new part here!
  and unit in (select unit from new_table
               where type = :user_parameter_type
              )
That approach is much more flexible than the current one (hard coded unit values within the report) because - units may change. What will you do the? Modify all reports that use that list of units?
Re: Lexical Parameter Slow down query [message #443558 is a reply to message #443500] Tue, 16 February 2010 04:50 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

thanks a lot Sir Littlefoot,

I have changed my report and linked it with a table. Report slows when i add Order by clause in query.

I am sorting data on date and I have an index on date in table.

what should do now?

thanks
Asif.

Re: Lexical Parameter Slow down query [message #443562 is a reply to message #443558] Tue, 16 February 2010 05:08 Go to previous message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Suppose you have a basket full of billiard balls. Now you shake them out. How fast do you do that? Quite fast!

Next try: you want to sort them by numbers. How fast will you do that? Much slower than previously!

ORDER BY *will* slow things down.

Perhaps you could set the "Break order" field property (within the Property Inspector window) for those fields (and omit ORDER BY in SELECT statement), and see what happens.
Previous Topic: Case Limitations
Next Topic: Passing IN parameter of datatype varchar2 (merged 5)
Goto Forum:
  


Current Time: Fri Sep 30 21:02:36 CDT 2016

Total time taken to generate the page: 0.05408 seconds