Multiple Outer Joins - Performance Problem

[Andy]

Situation:
My database is used to provide two reports from payroll
data. All tables are local (not ODBC) and are properly
indexed/keyed.
Using VB, I am creating individual queries for each pay
element for each employee. The individual queries are
connected in qryDeductions or qryIncomes depending on the
pay element type using outer joins such that qryDeductions
and qryIncomes returns EmployeeID for all employees and
values for all pay elements (obviously the value is null
if the employee did not receive pay for a given pay
element).
Next, all relevant employee data (date of birth, grade,
etc) is gathered in qryEmployeeDetails.
Finally, qryEmployeeDetails is combined with qryIncomes
and qryDeductions into qryEmpPayrollDetails

Problem:
There are approximately 15 deduction pay elements and 15
income pay elements thus qryDeductions contains
approximately 15 sub queries linked via outer join on
EmployeeID. If I reduce the number of deductions or
incomes to 12, qryEmpPayrollDetails returns a result set
in seconds. However, if I include all elements, it takes
more than 5 mintues. Performance Monitor shows 100% CPU
usage for the entire 5 minutes.

Question:
Does anyone out there have any suggestions to increase
performance?

 

[Jeff Boyce]

Andy

You've described a fairly complex tangle of queries. What you didn't
describe is the underlying table structure. Until we know that, it could be
tough to offer meaningful suggestions for improving query performance.

More info, please...

Jeff Boyce
<Access MVP>

 

[Dale Fye]

I'm assuming you are trying to do this because you want a separate
column in your table for each type of deduction, so you can have a
simple report with all the data in a single record.

You might want to consider doing this with a report/subreport
technique. Then you could use a Union query to join all your
deductions querys into qryEmployeeDeductionDetails which contains a
separate record for each detail type. The other advantage of this is
that you don't have to do any math to compute the total deductions,
you can let access do it in the sub-reports footer.

Another thought. You might be able to create a couple of intermediate
queries to group like items into a single query, then use these
intermediate querys to get your final query.

qryTaxes: join all the tax related deductions together in this
subquery.
qryInsurance: join all the Insurance related deductions together
qryFinancial:
--
HTH

Dale Fye


Situation:
My database is used to provide two reports from payroll
data. All tables are local (not ODBC) and are properly
indexed/keyed.
Using VB, I am creating individual queries for each pay
element for each employee. The individual queries are
connected in qryDeductions or qryIncomes depending on the
pay element type using outer joins such that qryDeductions
and qryIncomes returns EmployeeID for all employees and
values for all pay elements (obviously the value is null
if the employee did not receive pay for a given pay
element).
Next, all relevant employee data (date of birth, grade,
etc) is gathered in qryEmployeeDetails.
Finally, qryEmployeeDetails is combined with qryIncomes
and qryDeductions into qryEmpPayrollDetails

Problem:
There are approximately 15 deduction pay elements and 15
income pay elements thus qryDeductions contains
approximately 15 sub queries linked via outer join on
EmployeeID. If I reduce the number of deductions or
incomes to 12, qryEmpPayrollDetails returns a result set
in seconds. However, if I include all elements, it takes
more than 5 mintues. Performance Monitor shows 100% CPU
usage for the entire 5 minutes.

Question:
Does anyone out there have any suggestions to increase
performance?

 

[Andy Rutherford]

Jeff:

The most important data is held in only three tables:
tbl_pv_CoreData (employee data - 1 record per staff),
tbl_pv_PayWage (employee tax details - 1 record per
staff), and tbl_pv_PayElements (pay elements and amounts
by pay period - many records per staff).

I could easily provide a grouped report listing each pay
element for each employee by period as rows but this
information is being extracted from payroll and sent to
corporate for enterprise reporting so it has to be in a
specific format thus the pay elements need to be "rotated"
to become columns instead of rows.

tbl_pv_CoreData (pk=EmployeeID)
tbl_pv_PayWage (pk=EmployeeID, PayMonth, PayYear)
tbl_pv_PayElements (pk=EmployeeID, PayElementID, PayMonth,
PayYear)

Thanks,
Andy

 

[John Spencer (MVP)]

Andy,

You can use a query to get multiple rows for each employee and get the values.
Then use a crosstab query to rotate the data into columns. You do have a limit
of 255 columns.

I do something similar to this to rotate questionnaire data from a normalized
layout to a flat file layout to transfer the data to SAS for intensive
statistical analyis.

You may have to use a series of queries to do this, but I think you should need
no more than three to four queries total.