Board index » delphi » best sql

best sql


2006-06-22 10:01:20 PM
delphi103
what is better to do :
Select *
From
PROPERTY
JOIN TRANSAC ON PROPERTY.ID=TRANSAC.ID_PROPERTY
JOIN PROPERTY_PUBLICATION ON PROPERTY.ID=PROPERTY_PUBLICATION.ID_PROPERTY
or
Select *
From
TRANSAC
JOIN PROPERTY ON PROPERTY.ID=TRANSAC.ID_PROPERTY
JOIN PROPERTY_PUBLICATION ON PROPERTY.ID=PROPERTY_PUBLICATION.ID_PROPERTY
??
at the same what will be the best plan to use ?
Thanks by advance
stÚphane
 
 

Re:best sql

loki writes:
Quote
what is better to do :

Select *
From
PROPERTY
JOIN TRANSAC ON PROPERTY.ID=TRANSAC.ID_PROPERTY
JOIN PROPERTY_PUBLICATION ON
PROPERTY.ID=PROPERTY_PUBLICATION.ID_PROPERTY
or

Select *
From
TRANSAC
JOIN PROPERTY ON PROPERTY.ID=TRANSAC.ID_PROPERTY
JOIN PROPERTY_PUBLICATION ON
PROPERTY.ID=PROPERTY_PUBLICATION.ID_PROPERTY
at the same what will be the best plan to use ?
This entirely depends on the volume of records in each table, the indexes
and the selectivity of those indexes. Without knowing that there is now way
to guess which is going to be faster except by trying them yourself.
I tend to write my queries like your first query - with an eye to flattening
the relationships - i.e. the two joined tables are at the same level, both
linking to the main table. However, if I am suspicious of the plan it gives
me or the actual performance then I will certainly try alternatives such as
your 2nd example.
If, for the 2nd example, the join on PROPERTY_PUBLICATION will greatly
restrict the resulting rows from PROPERTY, then that may make the subsequent
join to TRANSAC more efficient.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
 

Re:best sql

Quote
what is better to do :

Select *
From
PROPERTY
JOIN TRANSAC ON PROPERTY.ID=TRANSAC.ID_PROPERTY
JOIN PROPERTY_PUBLICATION ON PROPERTY.ID=PROPERTY_PUBLICATION.ID_PROPERTY

or

Select *
From
TRANSAC
JOIN PROPERTY ON PROPERTY.ID=TRANSAC.ID_PROPERTY
JOIN PROPERTY_PUBLICATION ON PROPERTY.ID=PROPERTY_PUBLICATION.ID_PROPERTY
They should generate the same plan.
Ivan