The first query takes 9seconds to process, the 2second query takes
1seconds to process.
They return the same data. Why should you use an LEFT OUTER JOIN, or any
JOIN for that matter when you can say WHERE JOBINFO.JOBNUM =
ACTIVITYLOG.JOBNUM???
<cfquery name="ACTIVITIES" datasource="servalent" dbtype="ODBC">
SELECT JOBINFO.JOBNUM, JOBINFO.JOBNAME, JOBINFO.COMPANYNAME,
ACTIVITYLOG.LOGIN, ACTIVITYLOG.LOGOUT, ACTIVITYLOG.EMPLOYEENAME,
ACTIVITYLOG.EMPLOYEENUM, ACTIVITYLOG.ACTIVITYDESCRIPTION
FROM JOBINFO LEFT OUTER JOIN ACTIVITYLOG ON JOBINFO.JOBNUM =
ACTIVITYLOG.JOBNUM
WHERE ACTIVITYLOG.EMPLOYEENUM = #EMPLOYEENUM# AND
ACTIVITYLOG.LOGOUT is null AND
ACTIVITYLOG.LOGIN is not null
ORDER BY JOBINFO.JOBNUM, ACTIVITYLOG.LOGIN
</cfquery>
<cfquery name="ACTIVITIES" datasource="servalent" dbtype="ODBC">
SELECT JOBINFO.JOBNUM, JOBINFO.JOBNAME, JOBINFO.COMPANYNAME,
ACTIVITYLOG.LOGIN, ACTIVITYLOG.LOGOUT, ACTIVITYLOG.EMPLOYEENAME,
ACTIVITYLOG.EMPLOYEENUM, ACTIVITYLOG.ACTIVITYDESCRIPTION
FROM JOBINFO, ACTIVITYLOG
WHERE JOBINFO.JOBNUM = ACTIVITYLOG.JOBNUM AND
ACTIVITYLOG.EMPLOYEENUM = #EMPLOYEENUM# AND
ACTIVITYLOG.LOGOUT is null AND
ACTIVITYLOG.LOGIN is not null
ORDER BY JOBINFO.JOBNUM, ACTIVITYLOG.LOGIN
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists