db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Ideas for optimisation needed
Date Fri, 21 Dec 2012 13:10:06 GMT
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

Mime
View raw message