db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From J.Engl...@bton.ac.uk
Subject Re: Selecting first N rows of a result set
Date Mon, 09 Jan 2006 14:57:20 GMT
Bernt M. Johnsen wrote:
> Hi,
> Please post your SQL and JDBC code. That might make it possible to us
> to see whay you have performance problems.

The SQL is very simple: SELECT * FROM system_log_view
(although there may be optional search and ordering criteria as well, as
specified by the user, but the query in this form is the one that takes
a long time). I construct the query on the fly with holes for any extra
criteria, create a PreparedStatement and then execute it using the values
supplied by the user for any criteria. Without criteria, there are about
6000 records selected.

The view is a bit of a pig, due to date formatting and so on. The date
formatting is done so that users can refine the search by specifying
'20-Dec' or some such.

CREATE TABLE month_names (
   monthno     SMALLINT      NOT NULL PRIMARY KEY,
   monthname   CHAR(3)       NOT NULL

     (1, 'Jan'), (2, 'Feb'), (3, 'Mar'), (4, 'Apr'),
     (5, 'May'), (6, 'Jun'), (7, 'Jul'), (8, 'Aug'),
     (9, 'Sep'), (10,'Oct'), (11,'Nov'), (12,'Dec');

CREATE VIEW system_log_view AS
             RTRIM(CHAR(DAY(time))) || '-' || monthname || '-'
               || RTRIM(CHAR(YEAR(time))) || ' '
               || TIME(time) AS x_time,
             CASE WHEN system_log.username IS NULL
                  THEN '(system)'
                  ELSE surname || ', ' || initials END AS name,
             system_log.username AS username,
     FROM    system_log, users, month_names
     WHERE   (system_log.username IS NULL
              OR system_log.username=users.username)
     AND     monthno=MONTH(time);

I then want to be able to display pages of 20 rows at a time, which I do by
using res.absolute(n) to go to the first row of the page and then iterating
using res.next() 20 times.

> Generally in JDBC, the way to limit the number of rows returned from a
> query is stmt.setMaxRows(N).
>>>>>>>>>>>>>J.English@bton.ac.uk wrote (2005-12-15
>>I know this has been asked before, but I haven't been able to locate
>>the answer, so can anyone supply me with at least a link to it? I
>>have a table (currently about 5000 rows, each fairly large) and I
>>select some or all of those (by default, all of them, and I can then
>>refine it down), but I display the selected rows in pages of 20 at a
>>time. It's getting horribly slow now the table has grown, and I
>>wonder if selecting just 20 rows rather than a result set of 5000
>>will give me more acceptable performance...

  John English              | mailto:je@brighton.ac.uk
  Senior Lecturer           | http://www.it.bton.ac.uk/staff/je
  School of Computing & MIS | "Those who don't know their history
  University of Brighton    |  are condemned to relive it" (Santayana)

View raw message