Subject RE: [firebird-support] Query with few reads is slower than query with many reads. Which query to choose?
Author Leyne, Sean
> 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):


> 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?

Not in-and-of themselves, your first query suffers because of the high amount of random disk IO which your first query could generate.

In your testing, have you excluded the influence of FB and OS caches on your results?

If you run the first query, followed by the second, the results of the second query will benefit from the first query loading database pages into OS and FB caches. This can significantly influence/improve the results.