Re: need cursor info
On 27.11.2006 13:01, Martin Gregorie wrote:
Robert Klemme wrote:
I completely forgot about *that* option. Thanks for pointing it out.
That seems even less good practice to me - and I cannot remember the
last time I have actually seen that.
This is slightly OT, because is non-Java. However, as knowing about this
gotcha may be useful to somebody, here goes:
There are some really evil uses for these constructs (SELECT * and using
column numbers) and some of these can cause really terrible performance.
Some time back I was asked to find the cause of astoundingly poor
performance (tens of seconds to retrieve one row from a properly indexed
Sybase table with a several thousand rows in it). The application with
the problem was written in MS Visual C++ and used the Microsoft
Foundation Classes (MFC) to access the database via ODBC. There was
nothing obviously wrong with source code apart from a "select *
from...." SQL statement, but this had sensible WHERE and ORDER BY
clauses which were both supported by indexes. Although the "*" was bad
practice I didn't see how it could affect performance. So, I turned on
ODBC tracing to see what was going on.
It turned out that the MFC library code had an, ahem, idiosyncratic way
of getting the column names so it could bind field buffers to them.
Instead of iterating through the metadata for the table with the ODBC
"get column name" function, which is well documented and fast, it did
the following:
1) edited the SELECT statement, removing the WHERE and ORDER BY clauses
and replacing them with "WHERE 1 = 1"
I believe this must be a typo. You probably found "WHERE 1 = 0" - if
not, then it's completely logical for the DB to do a FTS - actually
that's the fastest way to get *all* the records. And that's what you do
when writing "WHEN 1 = 1". :-)
2) Ran the edited statement against the database. This returned a line
containing the column names. No rows were returned.
3) The column names list was parsed and field buffers were bound to
each column.
4) The original query was run and the result set fetched and processed
through a cursor.
Step (2) was causing the performance hit. The "WHERE 1=1" clause caused
Sybase to sequentially scan the entire table before returning the column
headings. Needless to say, this took 99% of the total time: when the
query was run as written it was very fast. So, I rewrote the SELECT to
list the columns it needed. Performance was in line with expectations
and ODBC tracing showed that steps (1) to (3) were now omitted.
This sort of thing is pernicious because its performance impact won't be
apparent when run against the test database with its typically small
data set, but *will* bite the developers as data builds up to expected
volumes in the production database.
Now, in Java we don't have a real equivalent for MFC, but there *is* a
strong family resemblance between JDBC and ODBC, so its possible that
similar gotchas could be lurking in corners of JDBC.
Actually there are drivers with which you cannot get the column names
from a table via meta data, so once I actually had to resort to SELECT *
FROM table WHERE 1 = 0 in order to get the column names and types.
Fortunately I did not see the effects you mention. :-)
Kind regards
robert