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