Thread: PgSQL vs Ms.Access slow in search
Hello everybody!
First of all, sorry for my poor english. And now, the problem
that don't leave me sleep :). I'm using PgSQL as the data source of some
forms in MsAccess, with the current version of the ODBC driver. It
exports/imports tables right and fast. But... when I have the tables
"linked" (it's correct this word???), and i try to do a search (Edit->Find
or Ctrl-B) in any column of any table with more of 1.000 tuples:
first: the access seems to be dead thinking too much :)
second: the postmaster takes 99% of CPU
Why????? In numerics primary keys, the both works better, but most of
time, they dead.
I've startde postmaster from a shell, and I get:
2001-10-09 20:39:50 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:52 [6573] DEBUG: CommitTransactionCommand
(only 4 querys from each second?????)
I've tried to fin a solution by my self, but I couldn't :(. With MySQL ->
MyODBC -> Access the problem It's the same.
Thanks in advance from Spain...
_____ - - - - - - - - - Alejandro Alfonso Fernández - - - - - - - - -
/\___/\ Grupo Universitario de Informática - Diplomatura en Estadística
/__\ _\/ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
/_/\_\ - "Lo mejor de tu ordenador está entre tu silla y tu teclado" -
/_/ /\_\ Atari Php Asp Wap Sql Dhtml Swf JavaScript Java Xml C Awk Sed
\_\/ \_\ - Email: kawa@gui.uva.es - Web: http://www.gui.uva.es/~kawa/ -
L.Carrol: Hace falta que corras todo lo que puedas para seguir en el mismo
sitio. Si quieres ir a alguna parte, tendras que correr el doble de rapido!
*gpg*public*key->http://www.gui.uva.es/%7ekawa/alexokawa.gpg<-gpg*public*key
Edit the ODBC driver DSN that you are using to connect to PostgreSQL and
turn on or enable the "CommLog" option. It can be found by clicking on the
"Driver" button. Try your search in Access again. This should create a
file in the C: drive of the PC you are running Access on called
c:\psqlodbc????.log where ???? is a number. If there are many of these
types of files in your C: drive, the select the latest file and review it's
contents. This should show everything that happens between Access and
PostgreSQL. This log file has helped me resolve 90% of my issues with
Access. If it is still unclear what the problem is, then please email the
log file.
Thanks, Michael Davis
Database Architect and Senior Software Engineer, Seva Inc.
Email: mdavis@sevainc.com
-----Original Message-----
From: Alejandro Alfonso Fernandez [SMTP:kawa@enete.gui.uva.es]
Sent: Wednesday, October 10, 2001 5:13 AM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] PgSQL vs Ms.Access slow in search
Hello everybody!
First of all, sorry for my poor english. And now, the problem
that don't leave me sleep :). I'm using PgSQL as the data source of some
forms in MsAccess, with the current version of the ODBC driver. It
exports/imports tables right and fast. But... when I have the tables
"linked" (it's correct this word???), and i try to do a search (Edit->Find
or Ctrl-B) in any column of any table with more of 1.000 tuples:
first: the access seems to be dead thinking too much :)
second: the postmaster takes 99% of CPU
Why????? In numerics primary keys, the both works better, but most of
time, they dead.
I've startde postmaster from a shell, and I get:
2001-10-09 20:39:50 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: CommitTransactionCommand
2001-10-09 20:39:51 [6573] DEBUG: StartTransactionCommand
2001-10-09 20:39:52 [6573] DEBUG: CommitTransactionCommand
(only 4 querys from each second?????)
I've tried to fin a solution by my self, but I couldn't :(. With MySQL ->
MyODBC -> Access the problem It's the same.
Thanks in advance from Spain...
_____ - - - - - - - - - Alejandro Alfonso Fernandez - - - - - - - -
-
/\___/\ Grupo Universitario de Informatica - Diplomatura en
Estadistica
/__\ _\/ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
/_/\_\ - "Lo mejor de tu ordenador esta entre tu silla y tu teclado"
-
/_/ /\_\ Atari Php Asp Wap Sql Dhtml Swf JavaScript Java Xml C Awk Sed
\_\/ \_\ - Email: kawa@gui.uva.es - Web: http://www.gui.uva.es/~kawa/
-
L.Carrol: Hace falta que corras todo lo que puedas para seguir en el
mismo
sitio. Si quieres ir a alguna parte, -tendras que correr el doble de
rapido!
*gpg*public*key->http://www.gui.uva.es/%7ekawa/alexokawa.gpg<-gpg*public
*key
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Wed, 10 Oct 2001, Michael Davis wrote:
> Edit the ODBC driver DSN that you are using to connect to PostgreSQL and
> turn on or enable the "CommLog" option. It can be found by clicking on the
> "Driver" button. Try your search in Access again. This should create a
> file in the C: drive of the PC you are running Access on called
> c:\psqlodbc????.log where ???? is a number. If there are many of these
> types of files in your C: drive, the select the latest file and review it's
> contents. This should show everything that happens between Access and
> PostgreSQL. This log file has helped me resolve 90% of my issues with
> Access. If it is still unclear what the problem is, then please email the
> log file.
Thank's in advance to spend your time :). But I've never enabled CommLog
(I thing that postmaster and tcpdump will give more information).
I've diving in microsoft.com, and I've instaled the last Jet engine, but
the problem persists. I've also chage some parametres in regedit.exe.
I'll gave you information when I'll know something else. Thank everybody
:)
_____ - - - - - - - - - Alejandro Alfonso Fernández - - - - - - - - -
/\___/\ Grupo Universitario de Informática - Diplomatura en Estadística
/__\ _\/ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
/_/\_\ - "Lo mejor de tu ordenador está entre tu silla y tu teclado" -
/_/ /\_\ Atari Php Asp Wap Sql Dhtml Swf JavaScript Java Xml C Awk Sed
\_\/ \_\ - Email: kawa@gui.uva.es - Web: http://www.gui.uva.es/~kawa/ -
L.Carrol: Hace falta que corras todo lo que puedas para seguir en el mismo
sitio. Si quieres ir a alguna parte, tendras que correr el doble de rapido!
*gpg*public*key->http://www.gui.uva.es/%7ekawa/alexokawa.gpg<-gpg*public*key