[Info-Ingres] performance problem with query on a views

[Cecil Westerhof]

Karl & Betty Schendel wrote:

> At 11:11 PM +0200 5/31/07, Cecil Westerhof wrote:
>>I have a strange performance problem with views.
>>[snip]
> 
> 
>>The used query:
>>################################################################################
>>SELECT...
>>FROM     viewB a
>>,        viewA b
>>WHERE    b.realid            = -4400
>> AND b.year              = 5
>> AND b.month             = 7
>> AND b.dag              in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
>> AND b.fieldB            = a.fieldB
>> AND a.realid            = b.realid
>>ORDER BY day, hour, fieldH
>>################################################################################
>>
>>The values the script gives are:
>> 0.83
>> 0.69
>> 0.88
>> 10.03
>> 1.06
> 
> Well, I don't know why there would be so much variation here.
> It should be doing a key lookup in tableB and then an index
> lookup in tableA.  The large number is an outlier of some kind.

What do you mean with that?

 
>>Because of this they want the where part to be:
>>################################################################################
>>WHERE    b.id                = 4400
>> AND b.year              = 5
>> AND b.month             = 7
>> AND b.dag              in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
>> AND b.fieldB            = a.fieldB
>> AND a.id                = b.id
>>################################################################################
>>
>>I would think that this would not give a significant difference, but the
>>used times become:
>> 13.76
>> 10.45
>> 8.71
>> 8.26
> 
> You want b.realid here!  b.id is not a keyed field because your
> view maps it to -id.  I suspect that if you get QEP's for the two
> queries you will see that they are different.

This is what I told the client also, but they find this not acceptable. This
is a new -not designed by me- system. And they want to use the same queries
as in the old system.
Are you sure that there is no index used? It is a join off a table with 35
miljoin elements on a table with 42 miljoin elements. When no index is used
I would expect a longer execution time. But I am no dba, so I could be
wrong.
How do I get QEP's? I am not only not a dba, I do not know Ingres. This is
my only client that uses this. So my knowledge of Ingres is low.