On Tue, 22 Apr 2008 14:39:54 -0400, Terry wrote:
> Can anyone make a recommendation on how to design a Java system that
> will make SQL queries on a mySQL database that results in very large
> data sets? What can be done to improve performance? Is hybernate
> better than using straight JDBC?
>
Please define "very large".
My application stores e-mail messages in a database. Its standard search
query uses a join over tables storing an e-mail body, correspondents and a
subject line. There's a many:many relation****p between correspondents and
messages with the intersection table saying whether the correspondent is
sender or recipient of each message. Various 'where' clauses that limit
the search scope (by correspondent, title and date range) are added
depending on the search terms the user provides. All prime keys and search
terms are indexed.
- specifying no search terms gets all possible rows in the result set.
That retrieved 83,000 rows in 14 secs.
- specifying a correspondent's name with a "like '%david%" clause
returned 7,000 rows in 2 seconds.
I'm using Postgres 7.3, its matching JDBC driver and Java 6.05. The
machine isn't big: 512 MB RAM, 866 MHz and running Fedora 8.
The result set is used to populate the model underlying a JTable. The
query that populates the table only returns three displayable columns of
v/l text (To/from NAME, subject, date), i.e. enough to form a displayable
row in the table, plus a soft dbk. The much larger underlying structures
are only retrieved when selected from the table to display the full
message: it takes around a second to get all the data from a join on the
row containing the dbk, populate a JFrame and display it. This join is
over the four tables and typically accesses 6-10 rows in total.
I'm not claimimg this idea (store a minimal list and allow selecting from
it to pull back full details of the selected item) is the best solution
but it suits my application, hasn't caused memory problems so far and
gives acceptable performance.
--
martin@[EMAIL PROTECTED]
| Martin Gregorie
gregorie. |
org | Zappa fan & glider pilot
|
