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

[Karl & Betty Schendel]

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.

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

Karl