Re: requested Logfile

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
>