[source_error]

This is somthing thats bugging me:

If you need to run an sql statement where you join 3-4-5 ... (well, alot) of tables. Whats then the best form. Using joins like:
afwerking INNER JOIN (boorden INNER JOIN (diktes INNER JOIN (Hoofdcategorie INNER JOIN (Soorten INNER JOIN (subcategorie INNER JOIN werkstukken ON subcategorie.nummer = werkstukken.subcategorie) ON Soorten.nummer = werkstukken.soort) ON Hoofdcategorie.nummer = werkstukken.hoofdcategorie) ON diktes.number = werkstukken.dikte) ON boorden.nummer = werkstukken.boordmodel) ON afwerking.nummer = werkstukken.afwerkingopp

or the where clause
SELECT DISTINCT p.ProductID, p.Image, p.Price
FROM products AS p,
category_links AS c_l,
categories AS c,
brands AS b,
size_links
AS s_l,
sizes AS s,
colour_links AS co_l,
colours AS co
WHERE c_l.ProductID = p.ProductID
AND c.CategoryID = c_l.CategoryID
AND p.BrandID = b.BrandID
AND s_l.ProductID = p.ProductID
AND s.SizeID = s_l.SizeID
AND co_l.ProductID = p.ProductID
AND co.ColourID = co_l.ColourID


Anyone knows which form has best performances or knows some links to articles or tutorials that deal with the performance of these two forms?

[andrewst]

It's not about performance, it is really just a different style. The JOIN syntax is more modern, and more clearly shows readers how you are joining the tables. It probably also helps reduce the incidence of mistakes when writing queries, e.g. missing join conditions. But since both styles express the same join conditions, there will be zero impact on performance.

[source_error]

Thanks for the info.

Does this also goes when you use outer joins?

Someone stated that when he used outer joins, the responsetimes increased substantially. (It's not really a common query, so it's more in general that i was wondering if there are indeed such substatial performance differences)
My geuss is that by combining different outer and inner joins, the RDBM will create more and bigger views before building the recordset, then in the were clause form.
But i couldn't find any info on it.

Below are the querys he ran + responsetime.
code:--------------------------------------------------------------------------------
SELECT DISTINCT p.ProductID, p.Image, p.Price
FROM products AS p
RIGHT JOIN category_links AS c_l
ON c_l.ProductID = p.ProductID
INNER JOIN categories AS c
ON c.CategoryID = c_l.CategoryID
RIGHT JOIN brands AS b
ON p.BrandID = b.BrandID
RIGHT JOIN size_links AS s_l
ON s_l.ProductID = p.ProductID
INNER JOIN sizes AS s
ON s.SizeID = s_l.SizeID
RIGHT JOIN colour_links AS co_l
ON co_l.ProductID = p.ProductID
INNER JOIN colours AS co
ON co.ColourID = co_l.ColourID
--------------------------------------------------------------------------------


code:--------------------------------------------------------------------------------
SELECT DISTINCT p.ProductID, p.Image, p.Price
FROM products AS p,
category_links AS c_l,
categories AS c,
brands AS b,
size_links
AS s_l,
sizes AS s,
colour_links AS co_l,
colours AS co
WHERE c_l.ProductID = p.ProductID
AND c.CategoryID = c_l.CategoryID
AND p.BrandID = b.BrandID
AND s_l.ProductID = p.ProductID
AND s.SizeID = s_l.SizeID
AND co_l.ProductID = p.ProductID
AND co.ColourID = co_l.ColourID
--------------------------------------------------------------------------------
Running the first one takes an average of 7 seconds. Running the second query takes less than 1 second. It's almost instantaneous in fact.

[r937]

inner and outer joins are not comparable, because they return different results

outer joins by definition return unmatched rows

performance will depend in indexes


rudy

[source_error]

Ok. thanks for the info.