[AccessD] Basic Query Design: Results

Jürgen,

Now, the $64,000 question:  Did all the queries give you the same results?

Just kidding!  I'm interested to see that the Count query is quickest.
 It's certainly easier to understand...and should make for a lot less
coding!

Steve Erbach
Neenah, WI
http://TheTownCrank.blogspot.com


On 2/10/06, Jürgen Welz <jwelz at hotmail.com> wrote:
> 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
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>


--
Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security