Solved System Resources Exceeded - This query (1 Viewer)

sumdumgai

Registered User.
Local time
Today, 11:38
Joined
Jul 19, 2007
Messages
453
I need and will appreciate any help with this. I'm sure I'll have to clarify but here's a start to explaining my problem.

Running MS Office 365. When running this query, we get 'System Resources Exceeded' error message from MS Access. Table T1 has 11,000 records, T2 has 43,000 records, and T3 has 670,000 records.

SELECT T1.F1, T1.F2, T2.F1, T2.F2, T2.F3, T2.F4, T2.F5, T2.F6, T2.F7, T2.F8, T2.F9, T3.F1, T3.F2, Sum(T3.F3) AS SumOfF3, T3.F4
FROM T1 RIGHT JOIN (T3 INNER JOIN T2 ON T3.F2 = T2.F2) ON T1.F2 = T2.F9
GROUP BY T1.F1, T1.F2, T2.F1, T2.F2, T2.F3, T2.F4, T2.F5, T2.F6, T2.F7, T2.F8, T2.F9, T3.F1, T3.F2, T3.F4
HAVING (((T3.F1)="32") AND ((T3.F4)>=[Enter Month]));
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:38
Joined
Jan 23, 2006
Messages
15,364
Look at the Similar Threads at the bottom of the page for ideas.
Good luck.
 

sumdumgai

Registered User.
Local time
Today, 11:38
Joined
Jul 19, 2007
Messages
453
Thanks.
 

sumdumgai

Registered User.
Local time
Today, 11:38
Joined
Jul 19, 2007
Messages
453
May I ask another question related to this please? About query efficiency, let's say smallTab has 10,000 records, mediumTab has 100,000 records and bigTab has 1,000,000 records.

smallTab has field 'zip'
mediumTab has fields 'zip' and 'id'
bigTab has field 'id'

I want to select records where mediumTab.zip matches smallTab.zip and within that selection where mediumTab.id matches bigTab.id.
Is it more efficient to first join smallTab and mediumTab and then mediumTab to bigTab, or to first join mediumTab to bigTab and then mediumTab to smallTab?

Hope that makes sense.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:38
Joined
Feb 19, 2013
Messages
16,553
doesn't really make a difference as far as the joins are concerned, what matters is indexing. If your fields are not indexed then access can only do a sequential search. See this link for more info https://www.access-programmers.co.u...ing-is-important-for-good-performance.291268/

with regards your query, what might make a difference is to change your HAVING clause to a WHERE clause. Simplistically, using HAVING means all records are returned and then the criteria is applied, Using WHERE means only records that meet the criteria are returned. Having said that, test shows that Access tries to optimise the query. You should really only apply HAVING to columns which your are summing/counting/etc, not grouping

It also might be a more efficient to put your T3 table into a query with the criteria and then join that to the other tables - I don't know what your data looks like but perhaps something like



SELECT DISTINCT T1.F1, T1.F2, T2.F1, T2.F2, T2.F3, T2.F4, T2.F5, T2.F6, T2.F7, T2.F8, T2.F9, T3.F1, T3.F2, Sum(T3.F3) AS SumOfF3
FROM T1 RIGHT JOIN ((SELECT F2,sum(F3) as SumofF3
FROM T3 WHERE F1="32" and F4=[Enter Month]
GROUP BY F2) T3 INNER JOIN T2 ON (SELECT T3.F2 = T2.F2) ON T1.F2 = T2.F9
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:38
Joined
Jan 23, 2006
Messages
15,364
I found this link recently that drives home the importance of indexes and how they evolved.
As CJ mentioned indexing can be critical to performance. I recall many "performance improvement/quality reviews" where supplied code was modified to use indexing rather than sequential reads. Well worth investigating and conducting a few tests. There will be a "happy balance".
Good luck.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:38
Joined
Feb 19, 2013
Messages
16,553
interesting history lesson from Albert. If you think about it, Excel still works on that old basis of order, perhaps that's why excel developers can struggle with rdb concepts
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:38
Joined
Feb 28, 2001
Messages
26,999
Is it more efficient to first join smallTab and mediumTab and then mediumTab to bigTab

In some databases this might matter, but in the case of Access and a JOIN query, it does not matter.


Basically, no matter the order in which you present your tables, SQL needs to evaluate all FROM and JOIN clauses first to determine the total scope of what you are going to visit. So JOIN in any order. It will look at everything anyway.
 

ebs17

Well-known member
Local time
Today, 16:38
Joined
Feb 7, 2020
Messages
1,881
SELECT F1, F2, Sum(F3) AS SumOfF3, F4
FROM T3
WHERE F1="32" AND F4>=[Enter Month]
GROUP BY F1, F2, F4

Use this query as a subquery instead of table T3. This table is therefore significantly smaller. Use this to link the other two tables.

A join weighs about three times the weight of a filter, so it makes a lot of sense to first calculate and reduce the size of the table, especially since this table is by far the largest.
In addition, the list of fields to be grouped should become drastically smaller.

Eberhard
 

sumdumgai

Registered User.
Local time
Today, 11:38
Joined
Jul 19, 2007
Messages
453
Thank you all for contributing.

ebs17, do you mean your query should create a temporary table which a second query would reference, or should your query be a subquery within a query? Sorry, but I'm new to this and could you please give example SQL.
 

ebs17

Well-known member
Local time
Today, 16:38
Joined
Feb 7, 2020
Messages
1,881
Code:
PARAMETERS
   [Enter Month] INT
;
SELECT
   T1.F1,
   T1.F2,
   T2.F1,
   T2.F2,
   T2.F3,
   T2.F4,
   T2.F5,
   T2.F6,
   T2.F7,
   T2.F8,
   T2.F9,
   SQ.F1,
   SQ.F2,
   SQ.SumOfF3,
   SQ.F4
FROM
   T1
      RIGHT JOIN
         (
            (
               SELECT
                  F1,
                  F2,
                  SUM(F3) AS SumOfF3,
                  F4
               FROM
                  T3
               WHERE
                  F1 = "32"
                     AND
                  F4 >= [Enter Month]
               GROUP BY
                  F1,
                  F2,
                  F4
            ) AS SQ
               INNER JOIN T2
               ON SQ.F2 = T2.F2
         )
      ON T1.F2 = T2.F9

Are the table fields that are linked and grouped on indexed?

Eberhard
 
Last edited:

ebs17

Well-known member
Local time
Today, 16:38
Joined
Feb 7, 2020
Messages
1,881
@CL_London:
The idea is the same, the execution is not.

Code:
SELECT F2,sum(F3) as SumofF3 FROM T3 ...

In the query below, you can only use the fields that the subquery provides.

In addition, grouping in the main query should largely be omitted if you first reduce or better avoid duplication via the links.

One would work in a more targeted manner if one knew the data model and representative data in the tables as well as the use of the query result. Perhaps some of the fields in the list can be omitted.

Eberhard
 

sumdumgai

Registered User.
Local time
Today, 11:38
Joined
Jul 19, 2007
Messages
453
Thanks again to everyone. I settled on splitting the query into two. The first creates a table that sums a field and filters records. That reduces the search records by 90%. It needs to run once a week. The second, which is run often selects records based on 'month'. No more resource problems.
 

Users who are viewing this thread

Top Bottom