From: David Hartwig <daveh@insightdist.com>
To: Tim Bosinius <tim@bosinius.de>
To: pgsql-interfaces <pgsql-interfaces@postgreSQL.org>
Subject: Re: requested Logfile
Date: Tue, 02 Jun 1998 16:29:20 -0400
Message-ID: <357460A0.894EDCE9@insightdist.com> (raw)
References: <01BD8E5D.B0003BF0.tim@bosinius.de>
Tim,
I cleaned up the statement in your log file that is the responsible for your
poor response time:
SELECT id,name FROM bar WHERE
id = '3 ' AND name = 'pqr ' OR
id = '5 ' AND name = 'mno ' OR
id = '8 ' AND name = 'jkl ' OR
id = '4 ' AND name = 'ghi ' OR
id = '2 ' AND name = 'def ' OR
id = '1 ' AND name = 'abc ' OR
id = '1 ' AND name = 'abc ' OR
id = '1 ' AND name = 'abc ' OR
id = '1 ' AND name = 'abc ' OR
id = '1 ' AND name = 'abc ''
You must have specified a multi-part key. If you cut this query out and run
it in the psql monitor you will see that it is a problem in the backend
server. We are currenly discussing how to resolve the problem. Hopefully it
will be resolved in the next release. In the mean time. There is something
you can do for queries. Turn off "Recognize Unique Indexes", and relink the
tables in Access. When you relink do NOT select a primary key. This will
greatly speed your queries, but the table will be read only.
You can also Show OIDs and throw a unique index on oid. This will allow
updates and have a modest increase in speed.
Tim Bosinius wrote:
> Hi
>
> I included the log you requested. I have reduced the data to six rows to
> keep it short.
> I placed a line under the statement that took 50 seconds to complete.
> In Access97 I created a new database and connected the table bar via ODBC.
> Afterwards I opened the table to enter data. Here is the log result:
>
> conn=157031128, SQLDriverConnect(
> in)='DSN=Foo;DATABASE=foo;SERVER=192.168.44.1;PORT=5432;READONLY=0;PROTO
> COL=;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;'
> DSN info:
> DSN='Foo',server='192.168.44.1',port='5432',dbase='foo',user='test',pass
> wd='test'
>
> readonly='0',protocol='',showoid='0',fakeoidindex='0',showsysta
> ble='0'
> conn_settings=''
> conn=157031128,
> SQLDriverConnect(out)='DSN=Foo;DATABASE=foo;SERVER=192.168.44.1;PORT=543
> 2;UID=test;READONLY=0;PWD=test;PROTOCOL=;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;
> SHOWSYSTEMTABLES=0;CONNSETTINGS='
> Global Options: fetch=100, socket=4096, unknown_sizes=0,
> max_varchar_size=254, max_longvarchar_size=4094
> disable_opabcizer=1, unique_index=0, use_declarefetch=1
> text_as_longvarchar=1, unknowns_as_longvarchar=0,
> bools_as_char=1
> extra_systable_prefixes='dd_;', conn_settings=''
> conn=157031128, query=' '
> conn=157031128, query='BEGIN'
> conn=157031128, query='set DateStyle to 'ISO'; set geqo to 'OFF''
> Command response: 'SET VARIABLE'
> conn=157031128, query='declare SQL_CUR157089856 cursor for select oid from
> pg_type where typname='lo''
> conn=157031128, query='fetch 100 in SQL_CUR157089856'
> [ fetched 0 rows ]
> conn=157031128, query='close SQL_CUR157089856'
> conn=157031128, query='END'
> conn=157031128, query='BEGIN'
> conn=157031128, query='declare SQL_CUR157089856 cursor for SELECT Config,
> nValue FROM MSysConf'
> ERROR from backend during send_query: 'ERROR: msysconf: Table does not
> exist.'
> conn=157031128, query='ABORT'
> conn=157031128, query='BEGIN'
> conn=157031128, query='declare SQL_CUR157089856 cursor for SELECT bar.id
> ,bar.name FROM bar '
> conn=157031128, query='fetch 100 in SQL_CUR157089856'
> [ fetched 6 rows ]
> conn=157031128, query='close SQL_CUR157089856'
> conn=157031128, query='END'
> conn=157031128, query='BEGIN'
>
> conn=157031128, query='declare SQL_CUR157089856 cursor for SELECT id,name
> FROM bar WHERE id = '3 ' AND name = 'pqr ' OR id = '5 ' AND name =
> 'mno ' OR id = '8 ' AND name = 'jkl ' OR id = '4 '
> AND name = 'ghi ' OR id = '2 ' AND name = 'def ' OR id
> = '1 ' AND name = 'abc ' OR id = '1 ' AND name = 'abc
> ' OR id = '1 ' AND name = 'abc ' OR id = '1 '
> AND name = 'abc ' OR id = '1 ' AND name = 'abc ''
> ---------------
>
> conn=157031128, query='fetch 100 in SQL_CUR157089856'
> [ fetched 6 rows ]
> conn=157031128, query='close SQL_CUR157089856'
> conn=157031128, query='END'
> conn=157031128, SQLDisconnect
>
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: daveh@insightdist.com, tim@bosinius.de
Subject: Re: requested Logfile
In-Reply-To: <357460A0.894EDCE9@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