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).
[EMAIL PROTECTED] 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:[EMAIL PROTECTED]
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)
----------------------------------------------------------------------
