On Thu, 2005-09-01 at 13:58, Steve Crawford wrote:
> We have an old legacy app that connects to our PostgreSQL (7.4.6)
> database. It is an old Visual Basic/Business Objects (VB 6.0, MDAC
> 2.5) program that selects a group of records and locks them by
> setting a field to an "in-progress" status. The user then works on
> those records and when done, returns the batch.
>
> We are having a terrible performance problem that we have traced to
> inappropriate queries being sent to the server. Whenever a user
> requests a batch, the app first runs the appropriate query with a
> where clause. This query returns virtually instantly.
>
> Unfortunately, it follows this by a "select * from tablename" which
> may return well over 100,000 records. Even this query run via psql on
> my linux desktop takes less than a second but apparently the VB app
> has trouble choking down all the unnecessary data it has requested
> leaving the user waiting 15 seconds or more for the update.
Is that select * being used to COUNT the number of rows? If so, then do
a "select count(*)" which will take the db engine about as long, but it
won't need to transfer the data across.
If a select * is really needed, then look at at least using a cursor.