Subject Re: [firebird-support] Sudden increase in execution times
Author PenWin
Svein Erling Tysvær napsal(a):
> The only thing that I notice probably could be optimized, is that I doubt it will ever be useful to use an index for validfrom (due to your description). So, change to
> SELECT d.datakey, MAX(h.histkey) histkey
> FROM data d
> LEFT JOIN history h ON h.code=d.code AND h.deleted=0 AND
> h.validfrom+0<='now' AND h.validto+0>'now'
> WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
> GROUP BY d.datakey
That seems to solve the problem! I have recreated the 9-minute situation
from yesterday as well as I could and got 3.08 seconds with this
modified query! I will do more testing to be sure, but it looks very
promising indeed. Thanks a lot!