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