Re: Mysteriously slow query, and side-by-side installation of 8.1 and 8.4 ODBC drivers

Rob Richardson wrote:
> 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.

Could you please try the drivers on testing for 8.4.0201 at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

> 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