Hi, I am having trouble with query performance - maybe someone can
suggest a better way? (Using SS 1.5.3)

We have a table of ASSETS, which get depreciated every year, so for
each year the new value of each asset is added to an ASSETVALUES
table. If we write off an asset we add a record to a WRITEOFFS table
(the record has a fk to the ASSET pk). To list what the value of each
asset was when it was written off, we simply run:

SELECT MAX(AV.ASSETVALUE_SN) FROM WRITEOFFS WO
JOIN ASSETS A ON A.ASSET_SN = WO.ASSET_SN
JOIN ASSETVALUES AV ON AV.ASSET_SN = A.ASSET_SN
GROUP BY AV.ASSET_SN

This works fine and is sub-second (it also assumes the max(pk) will
always be the recent asset value - maybe conceptually dodgy (if the
pks didn't increase sequentially), but in practice this is fine ;-) ).
Next we want to sum all the assetvalues so we encase the select in the
following:

SELECT SUM(AV2.VALUEOFASSET) FROM ASSETVALUES AV2 WHERE
AV2.ASSETVALUE_SN IN (<select query above>)

Performance goes out the door. There are only 10 pk's returned by the
first select, but to run the entire thing takes 80 seconds (and that
is on a fast server, with only an average-sized database). If I take
the 10 pk's and manually put them into the 'IN' clause, the query is
again instant.

Is there a way to force the optimiser/parser or something to work more
efficiently?
And/or is there a better way to run this combination of grouping and
aggregate functions?

Thanks,
Phil