Re: requested Logfile
От | David Hartwig |
---|---|
Тема | Re: requested Logfile |
Дата | |
Msg-id | 357460A0.894EDCE9@insightdist.com обсуждение исходный текст |
Список | pgsql-interfaces |
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 >
В списке pgsql-interfaces по дате отправления:
Следующее
От: Bruce MomjianДата:
Сообщение: Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report