[Info-Ingres] Hard query is in the way of next query

[Roy Hann]

"Cecil Westerhof" <dummy at dummy.nl> wrote in message 
news:463c43b0$0$330$e4fe514c at news.xs4all.nl...
> We are using Ingres Linux Version II 3.0.2.
> I was asked to optimize some queries that where using views. The queries
> took up to 12 minutes to execute. I rewrote them not using views and the
> new queries execute in around 1/3 of a second. Not bad indeed. ;-}
> To show the change I wrote a script that for each query execute the old 
> and
> new one severall times and display some statistics. The strange thing that
> when I run then new query after the old query, the new query can take up 
> to
> 7,5 seconds to execute. Even when I put a sleep for 5 minutes between the
> queries. In this way I do not get a good statistic. What is happening here
> and what can I do about it?

There is an awful lot of potentially relevant information missing from your 
problem description, like (for starters) the view definition, your 
alternative query, and the table descriptions.

Are you invalidating the DMF cache between tests?  You need to do that to 
ensure a fair and reliable comparison.  To invalidate the cache (posting any 
modified pages first) SET TRACE POINT DM421 immediately before every test.

My guess is that your first very fast (sub-second) query just used data 
cached by a previous attempt, so your expectations are now excessively high.

Roy

Ingres Users Association Spring Conference, 6 June 2007, London
Secure your place by registering NOW via the IUA web site 
http://www.iua.org.uk/