I have a table containing about 22000 records defined as follows:
CREATE TABLE system_log (
id INTEGER GENERATED ALWAYS AS IDENTITY,
time TIMESTAMP DEFAULT NULL,
username VARCHAR(15),
facility VARCHAR(15) NOT NULL,
event VARCHAR(31) NOT NULL,
module VARCHAR(15),
test VARCHAR(255),
details VARCHAR(32000),
CONSTRAINT systemlog_pk PRIMARY KEY (id)
);
I format this for display using a view defined like this:
CREATE VIEW system_log_view AS
SELECT TimeFormat(time) AS x_time,
facility,
event,
details,
NameFormat(surname,initials) AS name,
system_log.username AS username,
module,
test,
time,
id
FROM system_log LEFT JOIN users
ON system_log.username=users.username
I then display the log using "SELECT * FROM system_log_view", which takes
about a minute to execute. (Taking out the join on the users table and just
displaying usernames rather than surnames and initials takes a few seconds
off, but not much.)
When I try to display it sorted into descending order of time (SELECT * FROM
system_log_view ORDER BY time DESC) I often (but not always) get a
java.lang.OutOfMemoryError. The "not always" is presumably down to overall
system loading, but it's running on a reasonably powerful machine (a 64 bit
Linux system with about 4G of memory and loads of disk space).
Can anyone suggest what I can do to prevent this, or at least to narrow down
the reasons for the error? My last-ditch "catch Throwable" handler doesn't
give me a stack frame dump so it's hard to tell what's really going on.
TIA,
----------------------------------------------------------------------
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)
----------------------------------------------------------------------