Re: [INTERFACES] Problems with queries on small/medium sized tables

Hello,

At 13.00 15/09/98 -0500, you wrote:
>I am having a problem with queries.
>
...
>The following query is used:
>    SELECT usage.*
>    FROM usage
>    WHERE
>    (((usage.yyyymmdd) Between 19980701 And 19980731)
>    AND ((usage.link)='swbt08')) OR (((usage.link)='swbtja'))
>    ORDER BY
>    usage.yyyymmdd, usage.hhmm, usage.stp, usage.link;
>
>When run from psql the answer is returned fairly quickly. When run from
>Access via ODBC, the system memory starts increasing until it gets to 60/70%
>on the single postgres process, and CPU is usually below 10% (normal queries
>shoot CPU up to 50 to 99 % and memory stays around 3 to 8 percent). Then the
>ODBC call fails and Access doesn't get any information.

the jet database engine often parses and rewrites queries to get simpler
ones. Sometimes these simpler queries contain many "OR" in the where clause
and the backend optimizer crashes on them. Use the trace feature of the
ODBC to see exactly which kind of queries are sent to the backend. To solve
it you have to use passthrough queries, but I have no idea how this can be
done in Access.

Hope it helps.

Bye!

    Dr. Sbragion Denis
    InfoTecna
    Tel, Fax: +39 39 2324054
    URL: http://space.tin.it/internet/dsbragio