Hard query is in the way of next query

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?

"Cecil Westerhof" 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/

Roy Hann wrote:

> "Cecil Westerhof" 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.

It was also a problem with internal memory.
It are (were) fairly complicated views. View 1 was on table a, view 2 was a
join on table a and b. And the query was a join on view 1 and 2. I defined
simpler views. View 1 is now on table a and view 2 only on table b. And the
query is a join on view 1 and 2. The speed improvement is between 300 and
1100. So that is not bad. ;-}
So in principle problem solved. I think there was a problem with the join on
the join.


> 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.

When I try to do this, I get 'You do not have permission to set trace
flags'. How do I get this permission? I am not a regular Ingres user. I was
just asked to solve this problem.


> 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.

First the improvement when running the query another time was only on the
new queries, but with more memory I also see a speed improvement in the old
query. But when the fifth time that the old query executes, it still takes
more as 300 seconds and the first new query is finished in less then a
quarter of a second, I still think you can say that the new query is a lot
more efficient. :-D