way too many tables (1 Viewer)

In my Access system, I noticed the performance slow down dramtically recently after adding some extra subqueries to my SQL.

There are some tables in this list that could be combined into 1 table by just adding a month identifier column instead but I'm not sure it would cut down on all of my joins.

Here is a short sample of the SQL:
I was recommended to use this method of selecting disctinct resultsets in a subquery and then joining to them from the Structure table. This was because of the way the grouping multiplied up the figures when joining between each table. The only link between a table is back to the Structure table.

SELECT {whatever}
FROM (((((((((((((((((
SELECT [Div Level Description], DivisionID
FROM Structure
GROUP BY [Div Level Description], DivisionID

) AS S
LEFT JOIN (SELECT DivisionID,
(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000 AS Months0To3,
(Sum([121To150])+Sum([151To180])) /1000 AS Months4To5,
(Sum([181To365])) /1000 AS Months6To12,
(Sum([MoreThan366])) /1000 AS [Months12+],
((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt,
(Sum([151To180])) / 1000 AS DebtAged5Months
FROM zarageddebt_CURR
GROUP BY DivisionID
) AS ZA ON ZA.DivisionID = S.DivisionID)

LEFT JOIN (SELECT DivisionID,
(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000 AS Months0To3,
(Sum([121To150])+Sum([151To180])) /1000 AS Months4To5,
(Sum([181To365])) /1000 AS Months6To12,
(Sum([MoreThan366])) /1000 AS [Months12+],
((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt,
(Sum([151To180])) / 1000 AS DebtAged5Months
FROM Acquisitions_zarageddebt_PREV
GROUP BY DivisionID
) AS NA_ZA ON NA_ZA.DivisionID = S.DivisionID)

LEFT JOIN (SELECT DivisionID,
etc.


I get this in the workplan!
Workplan:
---------------------------------------------
DATE: 03/19/07
VER: 4.00.8618

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- temp query ---

- Inputs to Query -
Table 'MSysObjects'
- End inputs to Query -

01) Restrict rows of table MSysObjects
using rushmore
for expression "ParentId=251658241"
then test expression "Type=6 And Connect Is Null"
store result in temporary table


---------------------------------------------
DATE: 03/19/07
VER: 4.00.8618

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- DisplayDivisions ---

- Inputs to Query -
Table 'Structure'
Table 'zarageddebt_CURR'
Table 'Acquisitions_zarageddebt_PREV'
Table 'zarageddebt_PREV'
Table 'zarageddebt_PREV-1'
Table 'zarageddebt_PREV-2'
Table 'baddebt_CURR'
Table 'Acquisitions_baddebt_PREV'
Table 'baddebt_PREV'
Table 'zarinvreg_CURR'
Table 'Acquisitions_zarinvreg_PREV'
Table 'zarinvreg_PREV'
Table 'zarinvreg_PREV-1'
Table 'zarinvreg_PREV-2'
Table 'zarinvreg_PREV-3'
Table 'zarinvreg_PREV-4'
Table 'zarinvreg_PREV-5'
Table 'zarinvreg_PREV-6'
- End inputs to Query -

01) Group table 'Structure'
02) Group table 'zarinvreg_CURR'
03) Outer Join result of '01)' to result of '02)'
using temporary index
join expression "S.DivisionID=ZI.DivisionID"
04) Group table 'zarinvreg_PREV-6'
05) Outer Join result of '03)' to result of '04)'
using temporary index
join expression "S.DivisionID=ZIMinus7.DivisionID"
06) Group table 'baddebt_PREV'
07) Outer Join result of '05)' to result of '06)'
using temporary index
join expression "S.DivisionID=BDMinus1.DivisionID"
08) Group table 'zarinvreg_PREV-5'
09) Outer Join result of '07)' to result of '08)'
using temporary index
join expression "S.DivisionID=ZIMinus6.DivisionID"
10) Group table 'Acquisitions_baddebt_PREV'
11) Outer Join result of '09)' to result of '10)'
using temporary index
join expression "S.DivisionID=NA_BD.DivisionID"
12) Group table 'zarinvreg_PREV-4'
13) Outer Join result of '11)' to result of '12)'
using temporary index
join expression "S.DivisionID=ZIMinus5.DivisionID"
14) Group table 'baddebt_CURR'
15) Outer Join result of '13)' to result of '14)'
using temporary index
join expression "S.DivisionID=BD.DivisionID"
16) Group table 'zarinvreg_PREV-3'
17) Outer Join result of '15)' to result of '16)'
using temporary index
join expression "S.DivisionID=ZIMinus4.DivisionID"
18) Group table 'zarageddebt_PREV-2'
19) Outer Join result of '17)' to result of '18)'
using temporary index
join expression "S.DivisionID=ZAMinus3.DivisionID"
20) Group table 'zarinvreg_PREV-2'
21) Outer Join result of '19)' to result of '20)'
using temporary index
join expression "S.DivisionID=ZIMinus3.DivisionID"
22) Group table 'zarageddebt_PREV-1'
23) Outer Join result of '21)' to result of '22)'
using temporary index
join expression "S.DivisionID=ZAMinus2.DivisionID"
24) Group table 'zarinvreg_PREV-1'
25) Outer Join result of '23)' to result of '24)'
using temporary index
join expression "S.DivisionID=ZIMinus2.DivisionID"
26) Group table 'zarageddebt_PREV'
27) Outer Join result of '25)' to result of '26)'
using temporary index
join expression "S.DivisionID=ZAMinus1.DivisionID"
28) Group table 'zarinvreg_PREV'
29) Outer Join result of '27)' to result of '28)'
using temporary index
join expression "S.DivisionID=ZIMinus1.DivisionID"
30) Group table 'Acquisitions_zarageddebt_PREV'
31) Outer Join result of '29)' to result of '30)'
using temporary index
join expression "S.DivisionID=NA_ZA.DivisionID"
32) Group table 'Acquisitions_zarinvreg_PREV'
33) Outer Join result of '31)' to result of '32)'
using temporary index
join expression "S.DivisionID=NA_ZI.DivisionID"
34) Group table 'zarageddebt_CURR'
35) Outer Join result of '33)' to result of '34)'
using temporary index
join expression "S.DivisionID=ZA.DivisionID"


Will moving some of the tables into 1 table actually improve the performance or do you think it is all the subqueries that cause the issue?

First, you have double-posted this question. Please don't do that. You, as the originator, can delete the other post.

Second, any time you start openeing sub-queries, you make the system work that much harder because every sub-query has its own context including its own buffer. The fewer subs you open, the better off you are.

Combining tables would, indeed, help, but not if you still need the same number of subqueries.

Just for sh|ts and giggles, can you open your task list to see how much memory access is consuming when it is running this query? And compare that to physical memory. What I hope you aren't doing is swapping or paging a lot through the page/swap file. Yes, you CAN do that - but when Windows swaps, the world stops. (Has to do with priorities of swap manager vs. user apps.)

I SURELY would suggest compressing the number of tables and finding a way to reduce the number of joins. Particularly if this is a large data set, the number of temporary pointer lists (to virtual joined records) must be staggering. I'll bet you need to compact this puppy a lot with that humongous query running every so often.

You have a lot of tables which (from the name) I'm guessing hold the same sort of info...

I'm guessing that

Table 'zarageddebt_PREV'
Table 'zarageddebt_PREV-1'
Table 'zarageddebt_PREV-2'

have the same structure?

as do ?

Table 'baddebt_CURR'
Table 'baddebt_PREV'

and

Table 'zarinvreg_PREV'
Table 'zarinvreg_PREV-1'
Table 'zarinvreg_PREV-2'
Table 'zarinvreg_PREV-3'
Table 'zarinvreg_PREV-4'
Table 'zarinvreg_PREV-5'
Table 'zarinvreg_PREV-6'

Am I right?

I would say that for a question of this nature, not just the SQL but specifying the table structure is important to know as well. There is no clear-cut answer when asking "do I have too many tables?" The answer lies in whether or not the tables and their relationships are constructed properly in order to maximize the way an RDBMS works.

But, as noted by Danny - there are some potential things to be gleaned from the SQL statement that hints at a major normalization problem. So, yes your design seems flawed and needs fixing. How, exactly, depends on a lot of factors including what you are tracking, why you are tracking it, what you want to get back out of it, and your business rules and the way you run your business.