Re: prepare,declare,open PROBLEM WITH INDEXES

mkuhn@rhlab.UUCP (Michael Kuhn) wrote: >Found something interesting with 4GL > > "prepare, declare, open" coding method and the relationship to "indexes" > >Sequence ONE > > create temp table . . . > create index . . . > > prepare "sql statement that joins permanent table with temp table" > declare > open > . > . > . > This will cause the sql statement TO USE the created index in a join > >Sequence TWO > > create temp table . . . > > prepare "sql statement that joins permanent table with temp table" > > create index . . . > > declare > open > . > . > . > This will cause the sql statement DO AN AUTOINDEX > >Could someone confirm that this will actually create another >index identical to the one that was previously created? > >I am having a really WIERD problem that appeared as a result of >a RECOMPILE of a module. I am wondering if this is somehow related. > >In this module the prepare's are done at the beginning for many >different sql statements. The temp tables are created just so >the prepare can be done and then they are dropped. > >Various control loops in the program drop, recreate and rebuild >temp tables, doing declares and opens in the process. > >As a second thought. What triggers the sql engine to use an >index when a temp table is involved? Does is figure it out >or should and "update statistics" be done on temp tables? > > > >-- >Michael J. Kuhn Computer Systems Consultant phone:410-254-7060 >Email: mkuhn@csd.clark.net, csd@clark.net > mkuhn@rhlab.com > c/o Baltimore Rh Typing Laboratory, Inc. phone:410-225-9595 Take note that an SQL statement would be optimised when the PREPARE is executed (one of the advantages of using a PREPARE is to save re-optimisation for queries which are repeated). In your example the index does not exist when the PREPARE is executed therefore the optimiser does not use the index which is created after the PREPARE. create the temp table, load the data, create the index, run update statistics (for the temp table) and then prepare. Having said that I notice you mentioned your temp tables are created just for the PREPARE's and then dropped, so loading data may not be appropriate. In this case the optimiser may make the wrong decision (with empty temp tables) and it may be better to either restructure your code or just avoid the prepare phase altogether. (c) 1996, All rights reserved. __________________________________________________________ | o o | | Gavin Nour: nourg@acslink.net.au | | Director: Select Software Solutions Pty Ltd. | | Secretary: Informix User Group NSW, Australia. | | Committee member: International Informix User Group. | | Mobile: +(61) 0419 803 113 | | o o | |__________________________________________________________|