sql server - SQL - Joining together summed data from multiple tables -

i've been having following problem days. i've kinda managed resolve it, performance bothers me.

basically have 1 table of persons, , 2 tables (debt , wealth) references person. debt/wealth tables can have multiple rows refer same personid.

i need outcome have persons listed summed debt , wealth own columns.

first let me represent tables 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 person p left join (select personid, sum(value) debtsum debt group personid) d on d.personid = p.id left join (select personid, sum(value) wealthsum wealth group personid) w on w.personid = p.id 

this query returns correct data, said, performance worries me. example if have added thousands of rows inside debt table person doesn't exist (e.g. personid = 5), takes longer execute query. guess sum data person well, though not needed result?

i'm using sql server 2008, though sql fiddle using mysql (if makes difference).

i'd appreciate tips on how improve performance of query. i'm running out of ideas.

well, here's how it, though i'd wager proper indexes have bigger impact on performance query structure:

edit post comments:

select id, sum(debtsum) debtsum, sum(wealthsum) wealthsum ( select p.id, d.value debtsum, null wealthsum person p left join debt d on d.personid = p.id union select p.id, null debtsum, w.value wealthsum person p left join wealth w on w.personid = p.id ) t group t.id 

you should have indexes on person.id, debt.personid, , wealth.personid