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