I'll let someone else tackle the issue of expectations, but I did have a few thoughts....

Left joins may be tedious to write, but performance is more important. I could reduce a 10 line query to 1 line. But if the 1 line query takes 45 minutes longer to run, its hardly worth it.

I'm not an expert but I would be leery of using that many correlated subqueries with that kind of volume. Afaik, correlated subqueries execute once for each value in the outer query. So _generally_ subqueries _tend_ to be less efficient than joins.

However, databases and optimizers differ. Some may be capable of generating more efficient plans for subqueries, resulting in better performance. While other optimizers might not, yielding poorer performance for subqueries. Especially when dealing with a large volume of data.

I would read up on your db specifically, and learn how it handles joins versus correlated subqueries. That way you could make more of an informed decision.

I don't know if that helps any, but there it is ;-)