Selects and dmf cache

R.Srinivasan (srini@itc.soft.net) wrote:
: Hello,
: 	Thanks a lot for the tips regarding 'Big SELECTS causing slow down..'
: I have tried out the suggested remedies and found INDEXING did help.
: Though forcing SECONDARY INDEX in the WHERE clause didn't improve the 
: performance very much.

This does depend on the index's you use and whether the optimiser can
intelligently use the indexes it has been given.  

: 	I tried a couple of times running an ABF test frame with the 
: dbmsinfo( cpu_ms,  _dio_cnt,  _et_sec) before and after the select to
: find out the difference, but I found the elapsed time, diskio,cpu time
: to vary each time. ( even when using a DEDICATED SERVER ) 
: Could any one throw some light on this?

You should find this to be the case as you will have cached information on
the first run and depending on your cache-size and qsf size, you will be
swapping *out* information and possible qep's (depending on the the number
of repeated queries and dbprocs you use).  The best bet is to run the tests
a number of times and take an average and highlight the best and worst times.

Jon
--
#include 
+--------------------Reply to jonm@ingres.com-------------------------+
| Then when the number of dwarfs dropped from 50 to 8. The other      |
| dwarfs looked *very* suspiciously at 'Hungry'                       |
+---------------------------------------------------------------------+
| Jon Machtynger(jonm@ingres.com)                                     |
| Bvd de la Woluwe 34 Bte. 13                                         |
| Brussels. Belgium.  Ph: 02-774 49 23 Fax: 02-773 28 09              |
+---------------------------------------------------------------------+

     Make sure that the dmf cache is flushed between each set of 
     measurements; setting trace point dm421 should accomplish this.
     
     Liam McCauley
     
     Liam_McCauley@QSP.co.uk
     DBA Group
     Quality Software Products
Ingres Q & A
To William's Home Page
Email William