Re: Set explain utility

David Williams wrote: > > In article <33A6397D.3519@west.co.za>, "Mark D. Stock" > <marks@west.co.za> writes > > > >If you do not use PREPARE, your statement is parsed and optimised by > >the OPEN. > > > >If you use PREPARE, your statement is parsed by the PREPARE, and > >optimised by the OPEN. > > > >Hope that helps, > > Set explain output is ONLY created when you actually open the cursor, > I tested this on my machine at work today (4GL 6.0x / SE 7.1x). Yes, that's when the statement is optimised. > You get syntax errors if you try to prepare something which is > not valid syntax (I've had these yesterday!). Yes, that's when the statement is parsed. > Doing > > DATABASE djw > > MAIN > DEFINE x CHAR(200) > SET EXPLAIN ON > LET x = "select * from site" > PREPARE p1 FROM x > DECLARE c1 CURSOR FOR p1 > END MAIN> > no sqexplain.out produced. Yes, not optimised yet. > Doing > > DATABASE djw > > MAIN > DEFINE x CHAR(200) > SET EXPLAIN ON > LET x = "select * from site" > PREPARE p1 FROM x > DECLARE c1 CURSOR FOR p1 > OPEN c1 > END MAIN> > Bingo! set sqexplain.out produced. Yes, optimised by the OPEN. > The optimizer DEFINATELY parses statements when they are prepared. Weeell, the optimiser doesn't parse the statement, the engine does. The optimiser only optimises. :-) > The optimizer ONLY produces sqexplain.out files when the cursor if > opened. Yes. > My next question is when is it optimizer, I assume when the cursor is > opened since otherwise a needed index could be dropped between when > the prepare is done and when the open is done... Yes. > Anyone able to give a definate statement about when optimization is > done? Is this definitive enough: If you do not use PREPARE, your statement is parsed and optimised by the OPEN. If you use PREPARE, your statement is parsed by the PREPARE, and optimised by the OPEN. ? I hope that clears that one up, ;-) -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+