IDS Next Version - back on topic

May I commit a heresy here, and try and drag something off-topic back onto the original topic? I was just thinking how nice it would be to have declarative PDQ built into stored procedures and views: 1. onconfig parameter to enable / disable this feature, default is disabled. 2. CREATE VIEW ... WITH PDQPRIORITY x; where 0 <= x <= 100 or x is CURRENTPDQ (the default -- take it from the current session) 3. CREATE PROCEDURE / FUNCTION ... WITH PDQPRIORITY x; where 0 <= x <= 100 or x is CURRENTPDQ (the default -- take it from the current session) 4. EXECUTE PROCEDURE ... WITH PDQPRIORITY x; where 0 <= x <= 100 or x is COMPILEDPDQ (the default -- take it from compile time) I suppose the tricky thing would be how to force PDQPRIORITY 0, as that would be the default you'd want to override. Perhaps something like "SET PDQPRIORITY 0 FORCE;"? Clearly, I haven't thought it through, but something like that would be helpful in a number of sites I've seen where you only want PDQ available rarely, and it's difficult to make all the developers use it correctly so it gets left off, rather than allowing the developers to break more stuff. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche A smile is a gift that is free to the giver and precious to the recipient. But giving someone the finger is free too, and I find it more personal and sincere. sending to informix-list

Obnoxio The Clown wrote: > May I commit a heresy here, and try and drag something off-topic back onto > the original topic? > > I was just thinking how nice it would be to have declarative PDQ built > into stored procedures and views: > > 1. onconfig parameter to enable / disable this feature, default is disabled. > 2. CREATE VIEW ... WITH PDQPRIORITY x; where 0 <= x <= 100 or x is > CURRENTPDQ (the default -- take it from the current session) > 3. CREATE PROCEDURE / FUNCTION ... WITH PDQPRIORITY x; where 0 <= x <= 100 > or x is CURRENTPDQ (the default -- take it from the current session) > 4. EXECUTE PROCEDURE ... WITH PDQPRIORITY x; where 0 <= x <= 100 or x is > COMPILEDPDQ (the default -- take it from compile time) > > I suppose the tricky thing would be how to force PDQPRIORITY 0, as that > would be the default you'd want to override. Perhaps something like "SET > PDQPRIORITY 0 FORCE;"? > > Clearly, I haven't thought it through, but something like that would be > helpful in a number of sites I've seen where you only want PDQ available > rarely, and it's difficult to make all the developers use it correctly so > it gets left off, rather than allowing the developers to break more stuff. > If there are new PDQ values, then the optimiser would have to re-optimise. If a stored procedure is called within a transaction, then there could be a myriad (yes, lovely word) of permutations of locking issues ... originally catalogued at PDQPRIORITY 100 : e.g. TX1 === begin work; execute procedure with pdqpriority 0;(Humm I will hold all of these sysprocplan locks 'cos I have just been re-optimised) TX2 === begin work; execute procedure with pdqpriority 1;(Humm I want to re-optimise but TX1 has still got the locks on sysprocplan etc. etc.) Maybe the Query plan could be local to the session!

TBP wrote: > Maybe the Query plan could be local to the session! Then you could get the optimizer taking up a significant portion of processing time. Our application (for example) uses lots and lots of stored procedures for every session. If each session had to recompile and re-optimize the query plan for each of these, the box would just spin, I fear. Not to mention the size of the system catalog tables, which is already a concern in 9.4 (Have you checked your sysprocedures? Have you compared it to the same procedures in 7.3?) I think it would need to be more like overloading UDR's based on input parameters; just as you have one definition for udr(char) and a different definition for udr(char, int), you would have a query plan for udr (char) with pdqpriority 0 and a query plan for udr (char) with pdqpriority 1. Speaking of which, a means to pack and/or fragment system catalog tables would be nice, especally some of the ones which are growing out of all expectation. Sincerely, Christopher Coleman President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Medication Management Mediware Information Systems, Inc.