Hi,
> I recently analyzed a JDBC application
>for performance problems (using JProbe) and found that 2/3 of my time was
>being spent by the driver looking up my column number with the name.
>Always refer to columns by number, and read them all in one time -- first to
>last -- into a data structure from which you can randomly access what you
>need how you need it.
This is excellent advice; having had the same performance problems myself I can't
agree enough.
The primary problem with JDBC is the amount of String handling it does - in an
application where I was storing
and manipulating up to 80Mb of Strings in memory, and database access occurred on
approximately every
100th String access, the JDBC driver was responsible for more String object creation
and use than my portion
of the logic (which, being prototype at the time, did not bother to use StringBuffer
or any other optimizations).
My recommendations in this regard are fairly straightforward:
1. Use PreparedStatement wherever you can! Creating a new Statement for every
database access often
seems easier, but uses a LOT of resources, especially Strings. PreparedStatement is
also somewhat faster. On
the same rant, don't create new Statements every time - a Statement object can be
reused with different
queries.
2. Whenever you do SELECTs and the like, never use "SELECT *" - always name your
parameters (SELECT
alpha, beta FROM myTable). More than just the sheer issue of design and associated
problems if you modify
the table, you are guaranteed to have the columns returned in the listed order,
allowing you to safely use
column number indexing to retrieve values, and saving that really nasty overhead of
looking up column names
to get numbers (which, not surprisingly, creates an astonishing amount of Strings).
3. Cache your results and close the ResultSet as soon as possible. Remember that in
Java assigning a String
to another data structure does not make a new object - it assigns a reference to the
same String object that
JDBC created, because Strings are immutable. Typically only one ResultSet can be used
by a Connection at a
time, so this is prudent to improve the efficiency of multithreaded applications. Use
the cached results for as
long as possible, to prevent more JDBC calls.
4. Use SQL, not your own logic. Too many people fall prey to the "select everything
and then pick out what I
want" problem. Every SQL row you transfer from a database creates new objects. In
Java there is no reuse of
these objects, so retrieving 100 rows so that you can pick out five of them wastes a
lot of memory, and means
the garbage collector has to do a lot of extra work later. Use the WHERE clause, use
SORT, use GROUP BY
and use joins. People also have some sort of innate need to select from one table,
and then use that data to
select from another table, and so on. Maybe they need SQL tuition, or maybe they
somehow believe their
method is better. It isn't ... get over it ... use joins.
5. Linked to (4), only transfer what you need. If you only need the Username and
Password columns, don't use
"SELECT *" or also retrieve the user's real name and postal address, "on the off
chance" that you may need
them. Either you do or you don't.
Wells, thats my R2.
Given the exchange rate ... that's not far off 2c.
Shyte.
Twylite
