I am trying select contracts together with the contract lines. around 30.000
contracts and around 800.000 contract lines. And I have two queries that to
essentially the same (each contract has at least one contract_line):
select c.id
c.contract_date,
(select sum(cl.amount)
from contract_lines cl
where cl.contract_id=c.id)
from contracts c
where c.contract_date>='01.01.2015' and c.contract_date<='31.12.2015'
This query has few reads (around 10.000) but the execution time is very large.
In fact, the execution time itself is negligible, but fetch time is very large.
I guess - maybe Firebird is doing left join during execution time.
select distinct cl.contract_id
sum(sl.amount)
from contract_lines cl
join contracts c on (cl.contract_id=c.id)
group by cl.contract_id
This query has lot of reads (around 500.000) but the execution and fetch time
is very small.
All the reads of both queries are indexed reads.
The question is - which query to choose? I am afraid of many reads, because
they can create load on hard disk and that can slow down things for other
users. But from the other side - practice and low execution time shows that I
should use the second query.
Is large number of indexed reads harmful?
Jonatan
