ODBC and inappropriate select *
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
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.
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.
Beats me. WE are not requesting a "select *" at all in the VB code. We
are selecting and updating the rows we want to select and update via
the appropriate "where" information.
Somewhere in the black box that is VB/BusinessObjects/ODBC something
is deciding that a "select *" is necessary for reasons unknown and
then choking on (well, not actually choking but digesting slowly) the
data returned.
I'm just trying to find out if anyone has seen this problem and is it
something inherent in VB, BusinessObjects or ODBC? In other words, is
it something we can fix or do we need to set aside time to rewrite
the app in a language that doesn't have these problems?
Cheers,
Steve
Steve Crawford <scrawford@pinpointresearch.com> writes:
Somewhere in the black box that is VB/BusinessObjects/ODBC something
is deciding that a "select *" is necessary for reasons unknown and
then choking on (well, not actually choking but digesting slowly) the
data returned.
I'm just trying to find out if anyone has seen this problem and is it
something inherent in VB, BusinessObjects or ODBC?
If there is anyone around here who knows about it, you're more likely
to find them hanging out in pgsql-odbc ...
regards, tom lane
On Thu, 2005-09-01 at 15:21, Steve Crawford wrote:
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.Beats me. WE are not requesting a "select *" at all in the VB code. We
are selecting and updating the rows we want to select and update via
the appropriate "where" information.Somewhere in the black box that is VB/BusinessObjects/ODBC something
is deciding that a "select *" is necessary for reasons unknown and
then choking on (well, not actually choking but digesting slowly) the
data returned.I'm just trying to find out if anyone has seen this problem and is it
something inherent in VB, BusinessObjects or ODBC? In other words, is
it something we can fix or do we need to set aside time to rewrite
the app in a language that doesn't have these problems?
This sounds like the way access behaves, or at least used to, when using
a database other than MSSQL server. It's quite likely that whatever
VB/BusinessObjects is doing was written by the same poor sap who wrote
access's methods at the time, and just carried over that same brain dead
logic.
I'd check for updates to VB/WebObjects to see if there's a known problem
and / or fix with it.
I doubt it's ODBC in general, I've used that before, without this
problem, including on MS boxes. But you may have an old version that
was written, again, by the same guy who wrote access. Then who knows?