[Info-Ingres] Invalidate cache

[Roy Hann]

"Cecil Westerhof" <dummy at dummy.nl> wrote in message 
news:46512088$0$320$e4fe514c at news.xs4all.nl...
> Roy Hann wrote:
>
>> "Cecil Westerhof" <dummy at dummy.nl> wrote in message
>> news:465018e1$0$337$e4fe514c at news.xs4all.nl...
>>
>> If you have permission to run the accessdb utility, go into the "Users"
>> submenu, choose your user ID to edit it, and set the "Set trace flags"
>> switch to "y".  Don't forget to save the change.  Log back in to pick up
>> the new privilege.
>
> This works. Thanks.

Good.

> But it gives a new question.
> I run my queries on a machine that is doing nothing except executing my
> queries. Before every query I execute
>        SET TRACE POINT DM421
> But still there is a big difference between the queries. For example when
> executing one of the old queries five times it takes: 355,97, 690,16,
> 379,51, 100,27 and 60,96. When I execute the new version it takes: 2,60,
> 10,57, 0,99, 0,74, and 1,01 seconds. So it is save to say the new query is
> faster, but it is not clear by which factor. In the old query the
> difference between the fastest and slowest is 11,3 and in the new query it
> is 14,3. Is this normal?

You say you are running the same query repeatedly.  Is it exactly the same 
query each time, with exactly the same constants in the WHERE clause each 
time?   I have no explanation for why there would be a difference each time 
if it is the same query, except that perhaps the host machine is *very* busy 
doing other things as well.

However, if these are five similar queries, with the same SQL but different 
constants in the WHERE clause it could make perfect sense.  For instance a 
query like

   SELECT...WHERE date >= '20-may-2007' AND date <= '22-may-2007' AND...

will potentially involve much less data than

  SELECT...WHERE date >='01-jan-1970' AND date <= '31-dec-2035' AND...

To say anything more helpful you'd need to tell us the actual SQL, the size 
and structure of the tables and something about what the data looks like. 
Obviously a query that involves looking at only a handful of rows and joing 
to another handful is going to be much faster than the same query operating 
on tens of millions of rows.

There are a couple of presentations on our website that may be of use to 
you.  Go to www.rationalcommerce.com and click on the Presentations link, 
and from there download "Doing More with QEPs than Just Gawping" and "High 
Performance Batch Applications".

> One point, the testing is on a VMware virtual machine. Can the difference 
> be
> that big? Or is the performance of a virtual machine not constant?

The performance of any machine, virtual or otherwise, will be influenced by 
what else the hardware is trying to do at the same time.  My own experience 
with VMware running Ingres is limited to relatively light demand systems, 
but the performance difference is only measurable rather than noticeable 
when the hardware is lightly loaded.

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/