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.
Note: the end-users of the app may be remote and connecting via modem
but the query is running between the server-side and PG on a 100MB
connection.
We have checked the VB app and tried a couple changes without success.
Is anyone aware of any issue in VB or the ODBC driver that would
cause the DB to be hit by a "select *" query when none exists in the
app?
Cheers,
Steve