Обсуждение: 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?