ODBC and inappropriate select *

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: "pgsql-general postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: ODBC and inappropriate select *
Date: 2005-09-01 18:58:00
Message-ID: 200509011158.00093.scrawford@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2005-09-01 19:22:00 Re: query
Previous Message Poul Møller Hansen 2005-09-01 18:50:54 Check if SELECT is granted