We often have troubles with performance when joining tables with about 10 million rows together.
Sometimes we can't say what the cache engine is doing, it's just sucks and never finishes.
Then we have to rebuild the query and loop first on Orders and then on OrderPositions.
this is what we want:
select o.col1, o.col2, op.partnum, op.amount
from orders o join orderpositions op
where o.orderDate > $H-1000
this query does not finish...
then we rebuild and work with