--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Hi Phil!
>
> Why not simply get rid of the 'subaggregation'?
>
> SELECT SUM(AV2.VALUEOFASSET)
> FROM ASSETVALUES AV2
> WHERE NOT EXISTS(
> SELECT * FROM WRITEOFFS WO
> JOIN ASSETS A ON A.ASSET_SN = WO.ASSET_SN
> JOIN ASSETVALUES AV ON AV.ASSET_SN = A.ASSET_SN
> WHERE AV.ASSET_SN = AV2.ASSET_SN
> AND AV.ASSETVALUE_SN > AV2.ASSETVALUE_SN)
> AND EXISTS(
> SELECT * FROM WRITEOFFS WO
> JOIN ASSETS A ON A.ASSET_SN = WO.ASSET_SN
> WHERE AV2.ASSET_SN = A.ASSET_SN)
>
> Generally, the optimizer tries to transform all IN (<subselect>) into
> EXISTS(<subselect>). IN (SELECT MAX...) may be a situation where it
> isn't yet able to do so, and then your entire subselect is done once

for

> every record in ASSETVALUES - needless to say, an expensive and
> unneccessary operation. I believe the above query to be equivalent of
> what you wrote, but it should be much, much faster.
>
> Maybe you could speed up even further by taking some of the joined
> tables out of the subselects and into the main select, but I do not

know

> whether ASSET_SN is unique in ASSETS and/or WRITEOFFS or if there are
> records in ASSETVALUES that doesn't exist in ASSET/WRITEOFFS.
>
> HTH,
> Set

Thanks very much Set!
That was just what was needed, a fresh look and approach to my select.
With a few minor tweaks, your approach worked really well.

Cheers
Phil