db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lou DeGenaro <lou.degen...@gmail.com>
Subject slow query? (from a practical newbie)
Date Sun, 05 Jan 2014 15:12:53 GMT
My table: Job

 primaryKey int primary key generated always as identity,
 id int not null,
 startDate bigint not null,
 endDate bigint not null,
 duration int not null,
 userId varchar( 64 ) not null,
 submitter varchar( 64 ) not null,
 schedulingClass varchar( 64 ) not null,
 debugPortDriver int not null,
 debugPortProcess int not null,
 state varchar( 64 ) not null,
 stateIndex int not null,
 completionType varchar( 64 ) not null,
 completionRationale varchar( 1024 ) not null,
 services int not null,
 processes int not null,
 initFails int not null,
 runFails int not null,
 pgin int not null,
 swapGbMax double not null,
 memorySizeGb int not null,
 workItemsTotal int not null,
 workItemsDone int not null,
 workItemsError int not null,
 workItemsRetry int not null,
 workItemsPreempt int not null,
 workItemsStatMax int not null,
 workItemsStatMin int not null,
 workItemsStatMean int not null,
 workItemsStatStdDev int not null,
 description varchar( 4096 ) not null,
 schedulingSharesMax int not null,
 logDirectory varchar( 4096 ) not null,
 jsonServiceDependencies clob not null,
 jsonJobDeployment clob not null,
 projection bigint not null,
 swapGbCurrent double not null,
 workItemsDispatch int not null,
 constraint uniqueColumns unique ( id )


My indexes:

CREATE INDEX id_desc ON Job (id desc)
CREATE INDEX stateIndex_asc_id_desc ON Job (stateIndex asc, id desc)


Query #1 - fast! meaning sub-second using prepared statement

     select * from Job order by stateIndex asc, id desc offset ? rows fetch
next ? rows only


Query #2 - slow :-( meaning on the order of 30 seconds on a table with
55,000 records

     select * from Job where id < ? order by stateIndex asc, id desc offset
? rows fetch next ? rows only


Ostensibly the where clause really slow does the query.  How come and how
do I remedy?



View raw message