SQL - Joining together summed data from multiple tables

Question

I've been having the following problem for days. I've kinda managed to resolve it, but the performance is what bothers me.

Basically I have one table of persons, and two tables (debt and wealth) with references to person. debt/wealth tables can have multiple rows that refer to same personID.

I would need an outcome where I would simply have all the persons listed with summed debt and wealth as own columns.

First let me represent the tables I have:

Table 'person':

| ID | name    |
|----|---------|
|  1 | Adam    |
|  2 | Berg    |
|  3 | Carl    |
|  4 | David   |

Table 'wealth':

| ID | personID | value    |
|----|----------|----------|
|  1 |  1       |  100     |
|  2 |  1       |  2000    |
|  3 |  2       |  30000   |
|  4 |  3       |  400000  |
|  5 |  3       |  5000000 |

Table 'debt':

| ID | personID | value    |
|----|----------|----------|
|  1 |  1       |  100     |
|  2 |  1       |  2000    |
|  3 |  2       |  30000   |
|  4 |  2       |  400000  |
|  5 |  3       |  5000000 |

Expected result:

| personID | debtSum | wealthSum |
|----------|---------|-----------|
|  1       |    2100 |      2100 |
|  2       |   30000 |    430000 |
|  3       | 5400000 |   5000000 |
|  4       |  (null) |    (null) |

My solution:

SQL Fiddle

SELECT SQL_NO_CACHE p.ID, debtSum, wealthSum
FROM person AS p
LEFT JOIN (SELECT personID, SUM(value) AS debtSum FROM debt GROUP BY personID) AS d ON d.personID = p.ID
LEFT JOIN (SELECT personID, SUM(value) AS wealthSum FROM wealth GROUP BY personID) AS w ON w.personID = p.ID

This query returns the correct data, but as I said, the performance worries me. For example if I have added thousands of rows inside debt table for a person which doesn't exist (e.g. with personID = 5), it takes much longer to execute the query. I guess it does sum up the all data for that person as well, though it is not needed for the result?

I'm using SQL Server 2008, though the SQL Fiddle is using MySQL (if that makes a difference).

I'd appreciate tips on how to improve the performance of the query. I'm running out of ideas.

Answer 1

Well, here's how I would do it, though I'd wager proper indexes will have a bigger impact on performance than query structure:

EDIT POST COMMENTS:

SELECT ID, SUM(debtSum) AS debtSum, SUM(wealthSum) AS wealthSum
FROM (
SELECT p.ID, d.value AS debtSum, NULL AS wealthSum
FROM person AS p
LEFT JOIN debt d ON d.personID = p.ID
UNION ALL
SELECT p.ID, NULL AS debtSum, w.Value AS wealthSum
FROM person AS p
LEFT JOIN wealth w ON w.personID = p.ID
) t
GROUP BY t.ID

You should have indexes on Person.Id, Debt.PersonID, and Wealth.PersonID