Re: Ideas for optimisation needed

[John English]

On 20/12/2012 14:12, Jean-Yves Linet wrote:
Hi,
May you could give more details about the structure of your table.

The table looks like this:

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'm actually displaying a formatted view of the table:

CREATE VIEW system_log_view AS
  SELECT  TimeFormat(time) AS t_time,
          facility,
          event,
          details,
          NameFormat(username) AS name,
          username,
          module,
          test,
          id AS time
  FROM    system_log;

I had also suspected my formatting routines (TimeFormat, NameFormat), but I 
removed them and used "time as t_time" and "username as name" in place of the 
existing definitions of t_time and name. It made no noticeable difference.

Anyway the response delay you have seams to be very slow compare with what I
have with Derby.

It's much slower than I would expect too!

I always read my resultset as forward only.
Try to make a first request with select count(*) to have the number of rows.
and then a second request with an order by on time field and use only next() 
method.

Yes, I tried that too, and it also made no noticeable difference.

Thanks,
--
John English