Subject | RE: [firebird-support] Improving execution time |
---|---|
Author | Svein Erling Tysvær |
Post date | 2012-08-10T11:35:05Z |
>SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as sumweightdiffYou forgot to mention the main part of any optimization task, the chosen PLAN. This answer is therefore based on expecting indexes not to be optimized for this query yet.
>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
Is there any GRAVTIMESTAMP > 'NOW'? If not, then add a DESCENDING INDEX for GRAVTIMESTAMP and change your query to:
SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as sumweightdiff
FROM GRAVCOMPDATA_LONG r
where r.GRAVTIMESTAMP >= CAST('NOW' AS TIMESTAMP) - 60/1440e0
group by r.LINENR, r.COMPONENTNR
Hopefully, this will change the PLAN and the execution time improve.
HTH,
Set