Re: large queries

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 relationship 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@ | Martin Gregorie
gregorie. |
org | Zappa fan & glider pilot


.

Relevant Pages

  • Re: Why is this script so slow?
    ... | mysql database that has about 250 records in it. ... | When I use another script to extract the same data and display the ... | I can only conclude it is the script. ...
    (alt.php)
  • Re: Difference between storing files on folder and in mysql db
    ... a separate instance of an image display script (and a separate ... connection to the DB server) to display each image. ... connection if images were stored in the file system. ... mySQL database or if I should just store the files in a dedicated ...
    (comp.lang.php)
  • Re: Help in making links from mysql!
    ... > I have a mysql database with in the database i have ... > How can i display a link with only the title of the document stored?(easy ... You get the five last entries as you have done, ... You query first only the title and the id of the five last entries and ...
    (alt.php)
  • Simple From Generator accessing MySql
    ... I want a simple form generator in PHP that accesses a Mysql database. ... Options for display only, insert new record, and edit. ... I started writing my own, but I am out of time! ...
    (comp.lang.php)