[AccessD] Basic Query Design: Results

Had to try the speed comparisons between the three approaches to querying 
"Every Employee who has taken all of the following 4 courses".  Tables are 
tblEmployee, tblEmployeeCourse (junction table) and tblCourse.  There are 
1700 employees, about 2000 EmployeeCourse records and 18 course records.

1.  Using Count: qryTestCount

SELECT tblEmployeeCourse.EmployeeID, FirstName & ' ' & LastName AS Estimator
FROM tblEmployeeCourse INNER JOIN tblEmployee ON 
tblEmployeeCourse.EmployeeID = tblEmployee.ContactID
WHERE (((tblEmployeeCourse.CourseID) In (1,2,4,5)))
GROUP BY tblEmployeeCourse.EmployeeID, FirstName & ' ' & LastName
HAVING (((Count(tblEmployee.ContactID)) = 4))
ORDER BY FirstName & ' ' & LastName;

2.  Nested Selects: qryTestNestedIn

SELECT DISTINCT ContactID, FirstName & ' ' & LastName
FROM tblEmployeeEmployeeType RIGHT JOIN tblEmployee ON 
tblEmployeeEmployeeType.EmployeeID = tblEmployee.ContactID
WHERE tblEmployee.ContactID > 0 And tblEmployee.Deleted = False And 
FirstName > '' And LastName > ''  And ContactID In (Select ContactID FROM 
tblEmployee INNER JOIN tblEmployeeCourse ON tblEmployee.ContactID = 
tblEmployeeCourse.EmployeeID Where ContactID In (Select ContactID FROM 
tblEmployee INNER JOIN tblEmployeeCourse ON tblEmployee.ContactID = 
tblEmployeeCourse.EmployeeID Where ContactID In (Select ContactID FROM 
tblEmployee INNER JOIN tblEmployeeCourse ON tblEmployee.ContactID = 
tblEmployeeCourse.EmployeeID Where ContactID In (Select ContactID FROM 
tblEmployee INNER JOIN tblEmployeeCourse ON tblEmployee.ContactID = 
tblEmployeeCourse.EmployeeID Where CourseID = 1) And CourseID = 2) And 
CourseID = 5) And CourseID = 4)
ORDER BY FirstName & ' ' & LastName;

3.  Relational Division:  qryTestRelationalDiv

SELECT DISTINCT EC1.EmployeeID, [FirstName] & " " & [LastName] AS Expr1
FROM tblEmployeeCourse AS EC1 INNER JOIN tblEmployee ON EC1.EmployeeID = 
tblEmployee.ContactID
WHERE (((Exists (SELECT CourseID FROM tblCourse   WHERE CourseID In 
(1,2,4,5) AND NOT EXISTS      ( SELECT * FROM tblEmployeeCourse AS EC2       
   WHERE EC2.CourseID = tblCourse.CourseID         AND EC2.EmployeeID = 
EC1.EmployeeID)))=False))
ORDER BY [FirstName] & " " & [LastName];

The following subroutine was used to open each query in sequence and report 
to the debug window:

Public Declare Function timeGetTime Lib "Winmm" () As Long

Sub testQuerySpeed()
    Dim r As DAO.Recordset
    Dim db As DAO.Database
    Dim lngi As Long
    Dim t As Long

    Set db = CurrentDb
    t = timeGetTime
    For lngi = 0 To 50
        Set r = db.OpenRecordset("qryTestCount")
    Next
    Debug.Print "Using Count " & timeGetTime - t
    t = timeGetTime
    For lngi = 0 To 50
        Set r = db.OpenRecordset("qryTestNestedIn")
    Next
    Debug.Print "Using Nested Join " & timeGetTime - t
    t = timeGetTime
    For lngi = 0 To 50
        Set r = db.OpenRecordset("qryTestRelationalDiv")
    Next
    Debug.Print "Using RelationalDivision " & timeGetTime - t
    r.Close
    Set r = Nothing
End Sub

The results in the immediate window (in milliseconds), run 5 minutes apart 
speak for themselves:

Using Count 5219
Using Nested Join 34796
Using RelationalDivision 13563

Using Count 7750
Using Nested Join 53625
Using RelationalDivision 17000

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com