Query with Multiple Outer Joins

[BK]

I am trying to rework a database I made a couple years ago when frankly
I didn't know what I was doing in the slightest (I hope I know a little
bit more now). I'm trying to avoid redoing all the tables and such
(which may be unavoidable) but I am trying to run a query which seems to
be running kind of funny to me. The basic layout is I have a table
which has all JobNumbers which is the PK. I then have a bunch of other
tables related to this table which hold different costs and revenues. I
have a table for SubcontractorCosts, LaborCosts, EquipmentCosts,
MaterialCosts and Revenue. I set up a query with a bunch of outer joins
so I could get every job number and then the associated costs of all the
other tables. I have stored the costs because rates and such change and
I didn't want to setup a bunch of different subtables to track dates of
effective dates. When I run the query with four of the cost tables it
runs fine. Takes a few seconds, but no big deal. Now if I add the
fifth (and it doesn't matter which table) it just takes forever and a
day. In fact I have never seen it finish. Now I know outerjoins are
rather slow, but it's what I need and i'm not sure why the fifth is so
much worse than just the four. So is this a result of table design?
Query Design? Should I be approaching it a different way? The
JobNumber fields on all the related tables are indexed too. BTW, this
is an adp running off of SQL server although I upsized it to see if the
power of SQL server would improve it at all because it wouldn't work on
Access originally. No such luck.

Here's the query with the four:

SELECT TOP 100 PERCENT dbo.tblJobs.Job_Number,
SUM(dbo.tblLaborCost.TotalCost) AS LaborCost,
SUM(dbo.tblMaterialVendorCost.MaterialVendorCost)
AS MaterialCost,
SUM(dbo.tblEquipmentVendorCost.EquipmentVendorCost) AS EquipmentCost,
SUM(dbo.tblQwestInvoices.Amount) AS Revenue
FROM dbo.tblJobs LEFT OUTER JOIN
dbo.tblQwestInvoices ON dbo.tblJobs.Job_Number =
dbo.tblQwestInvoices.Job_Number LEFT OUTER JOIN
dbo.tblEquipmentVendorCost ON
dbo.tblJobs.Job_Number = dbo.tblEquipmentVendorCost.JobNumber LEFT OUTER
JOIN
dbo.tblLaborCost ON dbo.tblJobs.Job_Number =
dbo.tblLaborCost.JobNumber LEFT OUTER JOIN
dbo.tblMaterialVendorCost ON
dbo.tblJobs.Job_Number = dbo.tblMaterialVendorCost.JobNumber
GROUP BY dbo.tblJobs.Job_Number
ORDER BY dbo.tblJobs.Job_Number

And here it is with the five tables:

SELECT TOP 100 PERCENT dbo.tblJobs.Job_Number,
SUM(dbo.tblLaborCost.TotalCost) AS LaborCost,
SUM(dbo.tblMaterialVendorCost.MaterialVendorCost)
AS MaterialCost,
SUM(dbo.tblEquipmentVendorCost.EquipmentVendorCost) AS EquipmentCost,
SUM(dbo.tblQwestInvoices.Amount) AS Revenue
FROM dbo.tblJobs LEFT OUTER JOIN
dbo.tblSubcontractorCost ON
dbo.tblJobs.Job_Number = dbo.tblSubcontractorCost.JobNumber LEFT OUTER JOIN
dbo.tblQwestInvoices ON dbo.tblJobs.Job_Number =
dbo.tblQwestInvoices.Job_Number LEFT OUTER JOIN
dbo.tblEquipmentVendorCost ON
dbo.tblJobs.Job_Number = dbo.tblEquipmentVendorCost.JobNumber LEFT OUTER
JOIN
dbo.tblLaborCost ON dbo.tblJobs.Job_Number =
dbo.tblLaborCost.JobNumber LEFT OUTER JOIN
dbo.tblMaterialVendorCost ON
dbo.tblJobs.Job_Number = dbo.tblMaterialVendorCost.JobNumber
GROUP BY dbo.tblJobs.Job_Number
ORDER BY dbo.tblJobs.Job_Number

Any ideas?

TIA,
Bill

 

[BK]

I hate when I do this. Well I figured out a good way of doing it. I
made 5 separate queries. Each being one of the tables outer joined with
the jobnumber table. Then I did another query with innner joins of the
five queries. Works well.