Chad,
These two queries are NOT doing the same thing!
In your second query, the FROM statement and WHERE clause
FROM JOBINFO, ACTIVITYLOG
WHERE JOBINFO.JOBNUM = ACTIVITYLOG.JOBNUM
imply an INNER JOIN, not a LEFT OUTER JOIN. This will only select rows from
either table where the JOBNUM field on both tables match, which is an INNER
JOIN.
In your first query, the section
FROM JOBINFO LEFT OUTER JOIN ACTIVITYLOG ON JOBINFO.JOBNUM =
ACTIVITYLOG.JOBNUM
will return ALL records from JOBINFO, and for each of those records, it will
also return all records from ACTIVITYLOG where the JOBNUM field matches.
Any records on JOBINFO that DON'T have a matching record in ACTIVITYLOG will
still be returned, but the query row for that record will have NULLs in all
fields that come from ACTIVITYLOG.
That's why the LEFT OUTER JOIN query is taking longer.
You should always use explicit join statements in queries for three reasons
-
1) It makes the most of the DB engine's query optimiser - I inherited an old
site where all queries used the implied join, as in your second query. Just
going through and changing them all to explicit join statements cut the
execution time by (on average) 50%.
2) You can force join types to make the best use of memory/cpu resources (at
least on SQL server, anyway). Sometimes, really complicated queries can be
sped up by huge amounts, just by explicitly specifying a HASH JOIN.
3) It's much clearer syntax, and it allows you to separate the JOIN
conditions from the selection conditions - example:
SELECT Products.Description, Brands.Name AS BrandName, Categories.Title AS
CategoryTitle
FROM products
INNER JOIN Brands ON Brands.Code = Products.BrandCode
INNER JOIN Categories ON Categories.Code = Products.CategoryCode
WHERE products.description LIKE '%#search_string#%'
AND NOT(products.restricted = 1)
as compared to:
SELECT Products.Description, Brands.Name AS BrandName, Categories.Title AS
CategoryTitle
FROM products, brand, categories
WHERE products.brandcode = brands.code
AND products.categorycode = categories.code
AND products.description LIKE '%#search_string#%'
AND NOT(products.restricted = 1)
Hope that helps
Alistair Davidson
Senior Developer
Rocom New Media
www.rocomx.net
"There is no spoon"
-----Original Message-----
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: 18 September 2001 18:45
To: CF-Talk
Subject: SQL question about JOIN
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
