Обсуждение: Problems with queries on small/medium sized tables
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
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 >