Optimizing Query

[adhoustonj]

Hello AWF,
I'm trying to optimize multiple queries such as this one below. Does anything jump out that would help optimize this? I've been using the link below and identified my problem queries that take the longest. I'd like to approach this to achieve the quickest possible query run time.

There are not millions of records in these tables - employees (10,000), hs_area_fields (2,000), hs_fields(35), hs_area_fields_trn(100,000)

Speed tests and speed comparisons for everyone

The attached access file contains two tables, three queries, a module and a stopwatch class. If these elements are transferred to your own application, you will be able to carry out speed tests and speed comparisons for a large number of tasks without great effort. This solution is not as broad...

www.access-programmers.co.uk

SELECT afe.hs_area_id
    , afe.hsf_id
    , afe.hsaf_id
    , Count(IIf([tr_date] Is Null And [str_date]<[tblHS_area_fields]![aud_date],1)) AS notrain
    , Count(tblEmployees.emp_id) AS emp
    , IIf([notrain]=0,1,1-[notrain]/[emp]) AS trn_per
FROM (tblHS_fields
INNER JOIN (((SELECT af.*, tblEmployees.emp_id
        FROM (SELECT tblHS_area_fields.hs_area_id
            , tblHS_area_fields.hsf_id
            , Max(tblHS_area_fields.hsaf_id) AS hsaf_id
            FROM tblHS_area_fields
            GROUP BY tblHS_area_fields.hs_area_id, tblHS_area_fields.hsf_id)  AS af
        INNER JOIN tblEmployees ON af.hs_area_id = tblEmployees.hs_area_id
        WHERE (((tblEmployees.obsolete)=No) AND ((tblEmployees.no_hs_rec)=No)))  AS afe
LEFT JOIN tblHS_area_fields_trn ON (afe.emp_id = tblHS_area_fields_trn.emp_id) AND (afe.hsaf_id = tblHS_area_fields_trn.hsaf_id))
LEFT JOIN tblEmployees ON (afe.emp_id = tblEmployees.emp_id) AND (afe.hs_area_id = tblEmployees.hs_area_id)) ON tblHS_fields.hsf_id = afe.hsf_id)
INNER JOIN tblHS_area_fields ON afe.hsaf_id = tblHS_area_fields.hsaf_id
WHERE (((tblEmployees.no_hs_rec)=No) AND ((tblEmployees.obsolete)=No) AND ((tblHS_fields.trn)=Yes))
GROUP BY afe.hs_area_id, afe.hsf_id, afe.hsaf_id;

Thanks

 

[Josef P.]

Why is the 2nd access to tblEmployees needed?

You know the jet showplan?


BTW:

Count(IIf([tr_date] Is Null And [str_date]<[tblHS_area_fields]![aud_date],1)) AS notrain

Does this give a different result than Count(*)?

Sum(IIf([tr_date] Is Null And [str_date]<[tblHS_area_fields].[aud_date],1, 0)) AS notrain

 

[isladogs]

Have you looked at my own tests on optimizing queries which Eberhard adapted for his own purposes.
If not, start by looking at:

Speed Comparison Tests 8

This article covers various methods of optimising queries and the use of the JET ShowPlan feature. It is the eighth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your...

www.isladogs.co.uk

That is article 8 in a series of 15 articles. You may find other articles in the series also of interest

As Josef mentioned, you should learn how to use the JET ShowPlan feature to see how the query execution plans are organised.
You will find several links to that feature on my website as well
However, the feature does NOT work on subqueries

 

[adhoustonj]

Why is the 2nd access to tblEmployees needed?

You know the jet showplan?


BTW:

Count(IIf([tr_date] Is Null And [str_date]<[tblHS_area_fields]![aud_date],1)) AS notrain

Does this give a different result than Count(*)?

Sum(IIf([tr_date] Is Null And [str_date]<[tblHS_area_fields]![aud_date],1, 0)) AS notrain

I have not used the jet show plan. Let me see if I can dig into that.
The 2nd join to tblEmployees is to bring up the count of all employees.

The sum vs count gives the same results also but does runs a few ms quicker.

Test	Addition	Unit	Average	Minimum	Maximum	1	2	3	4	5	6	7	8	9	10
01_With Count	normal	milliseconds	1963.131​	1895.4093​	2145.6099​	1987.7323​	1961.0279​	1956.9928​	1935.7348​	1974.6264​	1936.74​	1916.6764​	2145.6099​	1895.4093​	1920.7607​
02_With Sum	normal	milliseconds	1927.9572​	1830.9001​	1980.4912​	1938.593​	1830.9001​	1934.5209​	1980.4912​	1962.8308​	1977.6651​	1926.8786​	1891.9739​	1926.8562​	1908.8623​

 

[isladogs]

I have not used the jet show plan. Let me see if I can dig into that.

See my answer above which was posted just before you replied

 

[adhoustonj]

See my answer above which was posted just before you replied

i'm checking this out now. Thanks!

 

[Josef P.]

The sum vs count gives the same results also but does runs a few ms quicker.

I don't think so.

Simple example:
TestTable:
X | N
A | 1
A | 2
A | 3

select
    X
    , Count(*) AS Cnt
    , Count(IIf(N>2,1,0)) AS Cnt_if
    ,   Sum(IIf(N>2,1,0)) AS Sum_if
from
     TestTable
group by
     X

Result:
A | 3 | 3 | 1

The 2nd join to tblEmployees is to bring up the count of all employees.

But is joined to afe.emp_id.

 

[plog]

Not best at optimization, but see some logical issues:

, IIf([notrain]=0,1,1-[notrain]/[emp]) AS trn_per

What's the purpose of testing notrain for 0 and converting it to 1 if so?

LEFT JOIN tblEmployees ON...
...
WHERE (((tblEmployees.no_hs_rec)=No) AND ((tblEmployees.obsolete)=No)...

When you apply criteria to a datasource you force it to be an INNER JOIN even though you explicitly state LEFT JOIN. Think about it, the only way no_hs_rec or obsoloete can be No is if a record is found in tblEmployees. If there is no record those values are NULL and they don't pass the criteria.

 

[adhoustonj]

I don't think so.

Simple example:
TestTable:
X | N
A | 1
A | 2
A | 3

select
    X
    , Count(*) AS Cnt
    , Count(IIf(N>2,1,0)) AS Cnt_if
    ,   Sum(IIf(N>2,1,0)) AS Sum_if
from
     TestTable
group by
     X

Result:
A | 3 | 3 | 1



But is joined to afe.emp_id.

This only returns 0 or 1, so sum and count produce the same results.

Count(IIf([tr_date] Is Null And [str_date]<[tblHS_area_fields]![aud_date],1)) AS notrain

 

[Josef P.]

Right, my mistake ... I mentally set the 3rd parameter of iif to 0 instead of Null.

select
    X
    , Count(*) AS Cnt
    , Count(IIf(N>2,1,NULL)) AS Cnt_if
    ,   Sum(IIf(N>2,1,0)) AS Sum_if
from
     TestTable
group by
     X

=>
A | 3 | 1 | 1

 

[Josef P.]

Count(tblEmployees.emp_id) AS emp

Should be the same as Count(*), since tblEmployees.emp_id will never be null.
Otherwise, I don't see any other use of tblEmployees in the outer selection anywhere. (But I am nearsighted. )

/edit:
I haven't used Jet as backend in a while . Does Jet follow the given brackets (from join) when creating the execution plan? (showplan shows the order)
If so, I would possibly move the sub-selection up, as this is likely the larger data limitation.

tblHS_fields inner join ( (...) as afe inner join ...)
vs
(..) as afe inner join (...)

... But these are only guesses, from someone who works mainly with SQL Server.


BTW: I assume that the indexes match.