This 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!
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.
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.
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.
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.
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.
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?
Thanks very much!
RobR
------_=_NextPart_001_01CB1DFA.99675D4A
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2900.5969" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2>Greetings!</FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial size=3D2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial size=3D2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial size=3D2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial size=3D2>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.
</FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial size=3D2>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. </FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial size=3D2>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?</FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial size=3D2>Thanks =
very
much!</FONT></SPAN></DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D203432117-07072010><FONT face=3DArial
size=3D2>RobR</FONT></SPAN></DIV>
<DIV> </DIV></BODY></HTML>
------_=_NextPart_001_01CB1DFA.99675D4A--
