It seems reasonable to me to use a join rather than a left join when you
know that all records in the secondary

tables will always be joined to the main table in your query, specially
since you know it leads to better performance.



The logical time to use a left join is when the relationship between the
main table fields and secondary table fields

is not guaranteed to exist and you want all records from the main table to
be returned by the query.



Graeme Edwards





_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Radu Sky
Sent: Wednesday, 25 January 2006 1:37 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Question on JOINS



Hello,

To be more explicit I will give an example from my current work.

I have three tables, COMPCLASS, COMPMARCA, COMPMODEL.

COMPCLASS
------------
CLASSID CLASS
1 Processor
2 Motherboard

COMPMARCA CLASSNO=FK of CLASSID
--------------
MARCAID CLASSNO MARCA
1 1 AMD
2 1 Intel
3 2 Epox
4 2 Asus
5 2 Gigabyte

COMPMODEL MARCANO=FK of MARCAID
---------------
MODELID MARCANO MODEL
1 1 XP2200+
2 1 Sempron3000+
3 2 Intel P4 2Ghz
4 3 8RDA3I
5 4 K8U-X


There are other tables which relate to the COMPMODEL table on MODELID
field, for example:

ENTRYPROD
------------
EID COMP(notnull) PRICE
1 5 324
2 2 34
4 1 53
etc

COMP field will always have a MODELID corespondent in the COMPMODEL
table, i have some triggers to insure consistency

My usual queries are something like

SELECT ENTRYPROD.*, CLASS, MARCA, MODEL
FROM ENTRYPROD
LEFT JOIN COMPMODEL ON COMP=MODELID
LEFT JOIN COMPMARCA ON MARCANO=MARCAID
LEFT JOIN COMPCLASS ON CLASSNO=CLASSID

This query seems the logical one, but for the sake of testing I was
doing something like

SELECT ENTRYPROD.*, CLASS, MARCA, MODEL
FROM ENTRYPROD
JOIN COMPMODEL ON COMP=MODELID
JOIN COMPMARCA ON MARCANO=MARCAID
JOIN COMPCLASS ON CLASSNO=CLASSID

which return the same amount of records (the actual tables have
thousands of records).
From the test I made, the query execution is fairly the same, however
the JOIN makes faster fetches.

LEFT JOIN (48662 records)
-------------
Prepare time = 0ms
Execute time = 20ms
Avg fetch time = 0.95 ms
Current memory = 2,746,816
Max memory = 2,846,608
Memory buffers = 10,240
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6,215


JOIN (48662 records)
-------------------
Prepare time = 10ms
Execute time = 10ms
Avg fetch time = 0.48 ms
Current memory = 2,746,476
Max memory = 2,846,608
Memory buffers = 10,240
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6,215



What is your opinion about these?

I hope I made myself understood

TIA
Radu





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



_____



[Non-text portions of this message have been removed]