Repeated queries

On Thu, 9 May 1996, Rick Raughton wrote:

>   It would be hard to beat the Ingres caches by second guessing the 
> queries.  Take a look at the trace points to see what the cache is doing
> and consider creating database procedures and rules for those common 
> routines everyone uses.  The queries will then be part of the database 
> engine and will not re-use the QEP.  

Fine advice--if curiously expressed.

> Also, if used carefully, the Repeat
> function will not re-run the QEP but you will want to restrict usage to 
> something like tablefield operations.

Not so sure what point you are trying to make here though, especially when
you just advocated using procedures which also save their QEP in the QSF
pool.  My rule of thumb for using REPEAT is this: 

 (1) if the optimization phase is a significant fraction of total response
     time, and
 (2) the query will be used "frequently" (ie often enough to keep the 
     plan from being tossed out of the QSF pool by the LRU algorithm when it 
     needs the space)
...then maybe it is worth flagging as REPEAT.

There is little or no point in taking up space saving a query plan that
takes 1 second to figure out from scratch every time, but then takes 5
minutes to execute.  Generally only the simplest queries profit from being
marked with REPEAT. 

REPEATed queries are also allowed to optimize longer, so you want to make
sure they don't get turfed out of the QSF pool frequently once they're in 
there, or they actually become counterproductive. 

I don't see how "tablefield operations" (as such) figure in it.

========================================================================
Roy Hann                                   rhann@tnc.com
BKB Engineering Ltd.,                      NAIUA Member-at-Large
11211-76th Avenue,                 
Edmonton, Alberta                          http://www.tnc.com/bkb
T6G 0K2                                    Tel: (403)497-2409
Canada                                     FAX: (403)436-6055
========================================================================
Ingres Q & A
To William's Home Page
Email William