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
);

INSERT INTO month_names VALUES
     (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
     SELECT  DISTINCT
             RTRIM(CHAR(DAY(time))) || '-' || monthname || '-'
               || RTRIM(CHAR(YEAR(time))) || ' '
               || TIME(time) AS x_time,
             facility,
             event,
             details,
             CASE WHEN system_log.username IS NULL
                  THEN '(system)'
                  ELSE surname || ', ' || initials END AS name,
             system_log.username AS username,
             module,
             test,
             time,
             id
     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
14:46:13):
>>
>>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)
----------------------------------------------------------------------

Mime
View raw message