Firebird SQL: query slow due to coalesce or can it be rewritten

Question

i'm having some performance problems with a frequently used query.

SELECT
v.id,
coalesce((SELECT sum(amount) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1 AND atype_ref in (1,3,4)), 0) "fv", 
coalesce((SELECT sum(amount) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1 AND atype_ref=2), 0) "ivo", 
coalesce((SELECT sum(amount) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1 AND atype_ref=5), 0) "iio", 
coalesce((SELECT sum(amount * mvalue) FROM artjournal WHERE variant_ref=v.id AND storage_ref=1), 0) "vw"
FROM productvariant v

since artjournal is a big table and gets thousands of new records each day the performance is getting terrible.

I have indices on all ID fields.

Is there a way to rewrite this statement to speed things up? Or can i use a different way to retrieve the data from the artjournal table and return 0 if result is null?

Thanks for your thoughts,

Christiaan

Answer 1

Looks like you want a filtered aggregate:

SELECT v.id,
       sum(case when a.atype_ref in (1,3,4) then a.amount else 0 end) as "fv",
       sum(case when a.atype_ref = 2 then a.amount else 0 end) as "ivo",
       sum(case when a.atype_ref = 5 then a.amount else 0 end) as "iio",
       sum(a.amount * a.mvalue) as "vw"
FROM productvariant v
  LEFT JOIN artjournal a ON a.variant_ref = v.id
WHERE storage_ref = 1
GROUP BY v.id;