[AccessD] Basic Query Design: Results

Of course, after I posted, I had to try another method.

The query is:

SELECT tblEmployee.ContactID, [FirstName] & ' ' & [LastName] AS Expr1
FROM (((tblEmployee INNER JOIN tblEmployeeCourse ON tblEmployee.ContactID = 
tblEmployeeCourse.EmployeeID) INNER JOIN tblEmployeeCourse AS 
tblEmployeeCourse_1 ON tblEmployeeCourse.EmployeeID = 
tblEmployeeCourse_1.EmployeeID) INNER JOIN tblEmployeeCourse AS 
tblEmployeeCourse_2 ON tblEmployeeCourse.EmployeeID = 
tblEmployeeCourse_2.EmployeeID) INNER JOIN tblEmployeeCourse AS 
tblEmployeeCourse_3 ON tblEmployeeCourse.EmployeeID = 
tblEmployeeCourse_3.EmployeeID
WHERE (((tblEmployeeCourse.CourseID)=1) AND 
((tblEmployeeCourse_1.CourseID)=2) AND ((tblEmployeeCourse_2.CourseID)=4) 
AND ((tblEmployeeCourse_3.CourseID)=5))
ORDER BY [FirstName] & ' ' & [LastName];


This is functionally equivalent to creating a query select where CourseID 
=1, another where CourseID = 2, a third where CouseID = 4 and a 4th where 
course ID = 5 and then joining them all on the ContactID.

A few runs of the time test adding this as a 4th test item result in a time 
that is 2nd fastest but consistently 70% slower than the fastest method that 
uses the count.


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





>From: "Jürgen Welz" <jwelz at hotmail.com>
>
>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
>
>


>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com