I'm by no means saying that my tests below are evidence enough to support the case for "chunky" SQL queries. By chunky, I mean queries that leverage subqueries (like the first example below) to bring in more data.
The execution times below however, supports chunky queries in every way.
- Faster – surprisingly
I would have expected the extra level of complexity to add some execution time. Quite the opposite. - Fewer queries
- Simplified script code to process the queries
What amazed me most about the execution times below is that the chunky query actually ran faster than the subquery within it.
Chunky Query (with subquery)
SELECT id, firstname, lastname, company,
(SELECT sum( price * quantity )
FROM order_items
WHERE orders_id = id ) AS subtotal
FROM orders
Query took 0.0255 sec
Two Queries to achieve the same data
SELECT id, firstname, lastname, company
FROM ordersQuery took 0.0007 sec
SELECT sum( price * quantity )
FROM order_items
Query took 0.0259 sec
Total: 0.0266 sec