Skip site navigation (1) Skip section navigation (2)

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

From: Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>
To: Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Mysteriously slow query, and side-by-side installation of 8.1 and 8.4 ODBC drivers
Date: 2010-07-08 21:48:17
Message-ID: 4C3647A1.5010507@tpf.co.jp (view raw or flat)
Thread:
Lists: pgsql-odbc
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

In response to

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2010-07-09 03:31:03
Subject: Re: 'default nextval()' loses schema-qualification in dump ?
Previous:From: Hiroshi InoueDate: 2010-07-08 21:29:35
Subject: Re: UPDATE statement value mutation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group