Hi everyone,

I am using FB 2.5.1 on Windows XP Prof.
Flamerobin as Admin tool.

I have the following table, in which every minute and on every line, a record is inserted.

CREATE TABLE GRAVCOMPDATA_LONG
(
LINENR integer NOT NULL,
COMPONENTNR integer NOT NULL,
GRAVTIMESTAMP timestamp NOT NULL,
THROUGHPUTACT float,
THROUGHPUTSET float,
EXTRRPM float,
SUMWEIGHT float,
HOPPERWEIGHT float,
CONSTRAINT INTEG_194 PRIMARY KEY (LINENR,COMPONENTNR,GRAVTIMESTAMP)
);

What I want to have is the following (e.g. the last hour)

LineNr ComponenNr SumWeightDifferences
1 1 257.470012
1 2 171.909999
1 3 85.290001
1 4 8.490000
1 5 17.080000
1 6 25.660000
2 1 518.069976
2 2 345.219987
2 3 173.059993
2 4 17.340000
2 5 34.509999
2 6 51.910000

This is achieved by this select:

SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as sumweightdiff
FROM GRAVCOMPDATA_LONG r where r.GRAVTIMESTAMP Between CAST('NOW' AS TIMESTAMP) - 60/1440e0 AND CAST('NOW' AS TIMESTAMP)
group by r.LINENR, r.COMPONENTNR

Running this leads to:

Starting transaction...
Preparing query: SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as sumweightdiff
FROM GRAVCOMPDATA_LONG r where r.GRAVTIMESTAMP Between CAST('NOW' AS TIMESTAMP) - 60/1440e0 AND CAST('NOW' AS TIMESTAMP)
group by r.LINENR, r.COMPONENTNR
Prepare time: 0.016s
Field #01: GRAVCOMPDATA_LONG.LINENR Alias:LINENR Type:INTEGER
Field #02: GRAVCOMPDATA_LONG.COMPONENTNR Alias:COMPONENTNR Type:INTEGER
Field #03: . Alias:SUMWEIGHTDIFF Type:DOUBLE PRECISION
PLAN (R ORDER INTEG_194)

Executing...
Done.
7397933 fetches, 296 marks, 1889395 reads, 214 writes.
0 inserts, 0 updates, 0 deletes, 2463735 index, 0 seq.
Delta memory: 1784340 bytes.
Total execution time: 13.250s
Script execution finished.


My question is : How can I improve execution time ?


When I do a select with defined LINENR=1 and COMPONENTNR=1 like this :

SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as sumweightdiff
FROM GRAVCOMPDATA_LONG r where r.GRAVTIMESTAMP Between CAST('NOW' AS TIMESTAMP) - 60/1440e0 AND CAST('NOW' AS TIMESTAMP) and r.LINENR=1 and r.COMPONENTNR=1
group by r.LINENR, r.COMPONENTNR

then the execution time is 0.032s

Starting transaction...
Preparing query: SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as sumweightdiff
FROM GRAVCOMPDATA_LONG r where r.GRAVTIMESTAMP Between CAST('NOW' AS TIMESTAMP) - 60/1440e0 AND CAST('NOW' AS TIMESTAMP) and r.LINENR=1 and r.COMPONENTNR=1
group by r.LINENR, r.COMPONENTNR
Prepare time: 0.016s
Field #01: GRAVCOMPDATA_LONG.LINENR Alias:LINENR Type:INTEGER
Field #02: GRAVCOMPDATA_LONG.COMPONENTNR Alias:COMPONENTNR Type:INTEGER
Field #03: . Alias:SUMWEIGHTDIFF Type:DOUBLE PRECISION
PLAN (R ORDER INTEG_194 INDEX (INTEG_194))

Executing...
Done.
319 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 102 index, 0 seq.
Delta memory: 25080 bytes.
Total execution time: 0.032s
Script execution finished.



Any help is appreciated.

Jörn