Very Computer

Hello,

We have a Sun machine with Ingres II 2.5.

We have a problem with the statistics. When we are running a query
with statistics (optimzedb -zk) the query is slower then without the
statistics. Is this a bug in Ingres. Has someone had the same problem
and how did you deal with it?

Thanks.

Kind Regards,

Michel

Hi Michel,

    Just putting statistics onto the tables concerned will not - and never has
    - garunteed 100% that the query will execute faster. This is as true in
    IngresII 2.5 as it was on Ingres 6.3.

    You may find many reasons why a default stats treatment would be better
    for your query. Skewed data is one, giving the optimizer way too much
    information is another. The latter case is interesting, as you can find
    the query executes faster but spends so much time being optimised the
    overall response is dreadful.

    It would help if you could run the query as a simple SQL session. If this
    is achievable, execute the query as:
    sql dbname << SQL_END
    \script qep_run.with_stats
    set qep; set optimizeonly;
    $Your_Query
    \p\g
    rollback
    \p\g
    \q
    SQL_END

    And then...
    sql dbname << SQL_END
    \script qe90_run.with_stats
    set qep; set trace point qe90;
    $Your_Query
    \p\g
    rollback
    \p\g
    \q
    SQL_END

    Do this with stats and without stats. Change the name of the files
    mentioned in the '\scripts' statements accordingly.

    You can drop the stats on a specific table using:
        statdump -zdl -udbowner dbname -rtablename

    It would also help if you do a:
        statdump -o stats.txt -zn3 -udbowner dbname -rtablename

    Do a help table on each of the tables involved as well.

    Hope this is of some help,

    Martin Bowes

Quote:

> Hello,

> We have a Sun machine with Ingres II 2.5.

> We have a problem with the statistics. When we are running a query
> with statistics (optimzedb -zk) the query is slower then without the
> statistics. Is this a bug in Ingres. Has someone had the same problem
> and how did you deal with it?

> Thanks.

> Kind Regards,

> Michel

--
Random Duckman Quote #40:
Duckman - Oh, you're a deaf mute. Well why didn't you say so!