From: Byron Nikolaidis <byronn@insightdist.com>
To: Davide Libenzi <dlibenzi@maticad.it>
Cc: pgsql-hackers@postgreSQL.org, pgsql-interfaces@postgreSQL.org, David Hartwig <daveh@insightdist.com>
Subject: Re: [HACKERS] ODBC and palloc ...
Date: Thu, 23 Jul 1998 14:50:03 -0400
Message-ID: <35B785DB.385009C4@insightdist.com> (raw)
References: <001001bdb66d$67aa33a0$1f0104c0@pcdavide>
Davide Libenzi wrote:
>
> I think this is not my case.
> See attachment log for details.
>
From looking at the log that is *exactly* your case. I pulled the
offending query out and cleaned it up a bit.
You have a two-part key (padre & figlio) and you can see the multiple
OR's between them. The MS Jet db engine typically uses a rowset size of
10 (so you see 10 keys below) and a keyset size of a couple of hundred
or so. In other words, it first read in 200 keys (the "keyset") and
then uses these keys to access a "rowset" of size 10 out of the entire
"resultset" (how ever many records you have total). This is called a
Mixed (Keyset/Dynamic) cursor or a "Dynaset". Like I said in my last
email, if you change the datasource to be read-only, then re-link your
table in Access, it will not use this style of retrieval and you should
get some results. OR, you can try the other options I mentioned.
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'PPPA' AND "figlio" = 'AAA'
OR "padre" = 'KKKL' AND "figlio" = 'LLLA'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
The only problem with this style of retrieving records is that the
Postgres backend can not handle it. It results in exponential memory
usage as it tries to optimize it. You could type in the above query by
hand to the monitor and see the same result.
Then for fun try rewriting the query to use UNIONS instead of OR's and
you will see how fast it is (assuming you have an index). See below.
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'PPPA' AND "figlio" = 'AAA'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'KKKL' AND "figlio" = 'LLLA'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
....
Byron
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: byronn@insightdist.com, dlibenzi@maticad.it, daveh@insightdist.com
Subject: Re: [HACKERS] ODBC and palloc ...
In-Reply-To: <35B785DB.385009C4@insightdist.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox