Mysteriously slow query, and side-by-side installation of 8.1 and 8.4 ODBC drivers
am 07.07.2010 19:34:05 von Rob RichardsonThis is a multi-part message in MIME format.
------_=_NextPart_001_01CB1DFA.99675D4A
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Greetings!
=20
We have a customer running PostgreSQL 8.1. I recently installed a new
version of their application. One piece of the application asks for a
single record from a 100,000-record table. In the previous version of
this application, the query worked well. Now, it takes 8 seconds.
Since the application can run the query 50 or more times, this is not
good. On the other hand, if the user is willing to wait for it, updates
to this table work.
=20
The ADO recordset is using a client-side cursor. If I change it to a
server-side cursor, the query takes only a few milliseconds. However,
updates no longer work. I get "query-based update failed because the
row to update could not be found". This is true even though there is
only one record in the recordset and the record includes the table's
primary key.
=20
I do not have this problem when running the application on my own
system, using a PostgreSQL 8.4 database and the latest ODBC driver. I
get good performance using either cursor location.
=20
A colleague explained the 8-second delay by saying that the entire table
is being downloaded to the client, and only after that does the where
clause of the query get applied. Someone on another list pointed me to
a Microsoft document that seemed to confirm that. I still do not
believe it, since the document said that "the entire result set" gets
downloaded to the client. In this case, the entire result set should
have consisted of a single record. =20
=20
The same colleague also suggested trying to install the latest ODBC
driver. I thought that after I did that, I would have a choice of
drivers when creating a new DSN. Instead, the 8.4 ODBC driver replaced
the 8.1 ODBC driver, and every time I tried to update anything, I got a
"multiple errors occured" error message. =20
=20
Can anyone suggest a reason for the ultra-slow single-record queries?
Can anyone tell me how to install the 8.4 ODBC driver so that it exists
alongside the 8.1 driver instead of replacing it?
=20
Thanks very much!
=20
RobR
=20
------_=_NextPart_001_01CB1DFA.99675D4A
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
charset=3Dus-ascii">
have a customer=20
running PostgreSQL 8.1. I recently installed a new version of =
their=20
application. One piece of the application asks for a single record =
from a=20
100,000-record table. In the previous version of this application, =
the=20
query worked well. Now, it takes 8 seconds. Since the =
application=20
can run the query 50 or more times, this is not good. On the other =
hand,=20
if the user is willing to wait for it, updates to this table=20
work.
ADO recordset is=20
using a client-side cursor. If I change it to a server-side =
cursor, the=20
query takes only a few milliseconds. However, updates no longer =
work. I=20
get "query-based update failed because the row to update could not be=20
found". This is true even though there is only one record in the =
recordset=20
and the record includes the table's primary key.
not have this=20
problem when running the application on my own system, using a =
PostgreSQL 8.4=20
database and the latest ODBC driver. I get good performance using =
either=20
cursor location.
colleague=20
explained the 8-second delay by saying that the entire table is being =
downloaded=20
to the client, and only after that does the where clause of the query =
get=20
applied. Someone on another list pointed me to a Microsoft =
document that=20
seemed to confirm that. I still do not believe it, since the =
document said=20
that "the entire result set" gets downloaded to the client. In =
this case,=20
the entire result set should have consisted of a single record. =20
same colleague=20
also suggested trying to install the latest ODBC driver. I thought =
that=20
after I did that, I would have a choice of drivers when creating a new=20
DSN. Instead, the 8.4 ODBC driver replaced the 8.1 ODBC driver, =
and every=20
time I tried to update anything, I got a "multiple errors occured" error =
message.
anyone suggest a=20
reason for the ultra-slow single-record queries? Can anyone tell =
me how to=20
install the 8.4 ODBC driver so that it exists alongside the 8.1 driver =
instead=20
of replacing it?
very=20
much!
------_=_NextPart_001_01CB1DFA.99675D4A--