db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jean-Yves Linet <jy.li...@gmail.com>
Subject Re: Ideas for optimisation needed
Date Fri, 21 Dec 2012 13:20:51 GMT
Do you have the same results if you exclude "details" field from your
columns list ?



2012/12/21 John English <john.foreign@gmail.com>

> 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