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

The FAQ covers this in some detail. See:
http://www.insightdist.com/psqlodbc/psqlodbc_faq.html#canon

Actually as I read the FAQ today I realize how little we knew at the time of
its writing. This is actually a manifestation of the MS Jet Engine which
breaks queries into key sets. The Jet is used in many MS products and data
widgets. I servrves the useful purpose of allowing large data sets to be
browsed without having to consume the entire queries or tables.

The actual failure is in the server. The 6.4 version of the server resolves
this problem quite effectively. It is in beta now and will be released in mid
Oct.

In the short term you can disable the Jet for any query in the query
properties. To browse a multi-part key table you will have to turn off auto
detection of the key in on of several ways. I will address this later if you
need. Time grows short tonight.

Greg Youngblood wrote:

> I am having a problem with queries.
>
> First, my setup. I have PostgreSQL 6.3.2 running on a dual PPro200 box with
> SuSE 5.2, Linux-SMP 2.0.35. The box has 64 meg RAM and 130 meg swap. I have
> a database that has three tables and several indexes. The two biggest tables
> have 331,200 rows of information. One of those has 5 columns, the other has
> 13 columns.
>
> I connect to the database via ODBC on a Windows NT 4 SP3 workstation, with
> Access 97. I am using the Insight Distribution Systems PostgreSQL ODBC
> driver version 6.30.0249.
>
> I can add data to the tables without any problems, however my queries seem
> to fail miserably, with ODBC call failed.
>
> Here's an example. I have the following columns
> YYYYMMDD HHMM SYSTEM USER USAGE
>
> where YYYYMMDD,HHMM,USAGE is int4, SYSTEM is char(16), and USER is char(32).
> YYYYMMDD, HHMM, SYSTEM, USER are all indexed individually, plus they are all
> used to create a unique key.
>
> 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.
>
> Also, using Access, when I have the database open, and the ables linked into
> my Access database, if I open the actual table itself, the same thing
> happens. The server sucks down memory to PostgreSQL until the ODBC call
> fails.
>
> Anyone have any ideas on what may be causing this? Or ways to fix this?
>
> Thanks
> Greg Youngblood
>
> Gregory S. Youngblood
> ext 2164
>