SET PDQPRIORITY in an IF...THEN block?
Question: does a SET PDQPRIORITY statement placed inside an IF...THEN block in a stored procedure actually control PDQ at runtime, or does its mere presence force PDQ use? Answer from the list: a procedure runs with the PDQPRIORITY in effect when it was created or last recompiled (update statistics for procedure), but an executed SET PDQPRIORITY inside the routine overrides that. So if the SP is compiled with PDQPRIORITY 0, conditional SET statements work as intended; if it was created while PDQPRIORITY 10 was set, it will always run at 10.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All: I have some SP code that has a SET PDQPRIORITY N directive inside an IF ... THEN block that checks the application configuration table to see whether PDQ is enabled. Can anyone (Art, ideally) confirm whether or not this will actually work to control PDQ and will NOT use PDQ if the IF block evaluates to false? I am getting objections to this from someone who seems to believe that the SP will use PDQ if a SET PDQPRIORITY directive appears anywhere in it at compile time... TIA -- John Hardin KA7OHZ Senior Applications Developer, BI Specialist EPICOR Retail web: http://www.epicor.com email: <jhardin@epicor.com> --- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---
On 02/08/12 18:59, John Hardin wrote: > All: > > I have some SP code that has a SET PDQPRIORITY N directive inside an > IF ... THEN block that checks the application configuration table to see > whether PDQ is enabled. > > Can anyone (Art, ideally) confirm whether or not this will actually work > to control PDQ and will NOT use PDQ if the IF block evaluates to false? I > am getting objections to this from someone who seems to believe that the SP > will use PDQ if a SET PDQPRIORITY directive appears anywhere in it at > compile time... > > TIA > That somebody is correct. SP's run with the PDQ setting in effect at creation / update statistics time. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Hi John, PDQ for stored procedures is set at creation/update statistics time. Executing a SET PDQPRIORITY statement within the stored procedure overrides that. Quote: "The PDQ priority value that the database server uses to optimize or reoptimize an SQL statement is the value that was set by a SET PDQPRIORITY statement, which must have been executed within the same procedure. If no such statement has been executed, the value that was in effect when the procedure was last compiled or created is used." See: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_603.htm Jason
On Thu, 02 Aug 2012 17:59:00 +0000, John Hardin wrote:
> I am getting objections to this from someone who seems to believe that
> the SP will use PDQ if a SET PDQPRIORITY directive appears anywhere in
> it at compile time...
That person has clarified, they were speaking of:
set pdqpriority 10;
create procedure abcd(...)
If this is done, and the application is configured to not use PDQ (e.g.
the embedded SET PDQPRIORITY N directives are _not_ executed because of
the IF test) will the SP still use PDQ?
Again, TIA.
--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
email: <jhardin@epicor.com>
--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---
Yes, the procedure will always run with PDQPRIORITY 10.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Aug 23, 2012 at 7:33 PM, John Hardin <jhardin@epicor.com> wrote:
> On Thu, 02 Aug 2012 17:59:00 +0000, John Hardin wrote:
>
> > I am getting objections to this from someone who seems to believe that
> > the SP will use PDQ if a SET PDQPRIORITY directive appears anywhere in
> > it at compile time...
>
> That person has clarified, they were speaking of:
>
> set pdqpriority 10;
> create procedure abcd(...)>
> If this is done, and the application is configured to not use PDQ (e.g.
> the embedded SET PDQPRIORITY N directives are _not_ executed because of
> the IF test) will the SP still use PDQ?
>
> Again, TIA.
>
> --
> John Hardin KA7OHZ
> Senior Applications Developer, BI Specialist
> EPICOR Retail
> web: http://www.epicor.com
> email: <jhardin@epicor.com>
>
> --- Posted via news://freenews.netfront.net/ - Complaints to
> news@netfront.net ---
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Thu, 23 Aug 2012 20:00:55 -0400, Art Kagel wrote:
> Yes, the procedure will always run with PDQPRIORITY 10.
Okay. Thanks, Art.
In the initial scenario, assuming the SP is /not/ created with PDQPRIORITY
in effect as shown below, does an IF block around a PDQPRIORITY statement
within the SP allow the SP to intelligently decide whether or not to use
PDQ at runtime?
> On Thu, Aug 23, 2012 at 7:33 PM, John Hardin <jhardin@epicor.com> wrote:
>>
>> set pdqpriority 10;
>> create procedure abcd(...)>>
>> If this is done, and the application is configured to not use PDQ (e.g.
>> the embedded SET PDQPRIORITY N directives are _not_ executed because of
>> the IF test) will the SP still use PDQ?
--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
email: <jhardin@epicor.com>
--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---
Yes. As long as the routine is compiled (ie created or recompiled with
update statistics for procedure/function) with PDQPRIORITY set to zero inthe compiling environment it will normally execute with the PDQPRIORITY of
the executing environment and will obey SET PDQPRIORITY statements embedded
in the procedure including conditional execution.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Aug 23, 2012 at 8:07 PM, John Hardin <jhardin@epicor.com> wrote:
> On Thu, 23 Aug 2012 20:00:55 -0400, Art Kagel wrote:
>
> > Yes, the procedure will always run with PDQPRIORITY 10.
>
> Okay. Thanks, Art.
>
> In the initial scenario, assuming the SP is /not/ created with PDQPRIORITY
> in effect as shown below, does an IF block around a PDQPRIORITY statement
> within the SP allow the SP to intelligently decide whether or not to use
> PDQ at runtime?
>
> > On Thu, Aug 23, 2012 at 7:33 PM, John Hardin <jhardin@epicor.com> wrote:
> >>
> >> set pdqpriority 10;
> >> create procedure abcd(...)> >>
> >> If this is done, and the application is configured to not use PDQ (e.g.
> >> the embedded SET PDQPRIORITY N directives are _not_ executed because of
> >> the IF test) will the SP still use PDQ?
>
> --
> John Hardin KA7OHZ
> Senior Applications Developer, BI Specialist
> EPICOR Retail
> web: http://www.epicor.com
> email: <jhardin@epicor.com>
>
> --- Posted via news://freenews.netfront.net/ - Complaints to
> news@netfront.net ---
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>