Option number 3 :). The following information is gathered using a 1.0.3
server.

I once created a table like this with 20 million records to test the speed.
Number 1 is the poorest, it would result in a sequential search (I read it
from the plan). Number 2 is better, at the beginning I think it is the
fastest. But not when I changed the query by using sub-select.

Select t1.field1, t1.field2, (select field2 from t2 where t2.field1 =
t1.field2), ....

I remember it can return a query in about 14 seconds. Option 2 about 1
minute. Using option 1 and the machine hang up (slowwww) on creating temp.
data.

Is the engine now optimize option 2 in 1.5 or 2.0 ? Anybody comment.

Best regards,

Almond Wong


At 02:53 2005-05-04, Edson T. Marques wrote:

>If my data base has a table that is related with ten other tables and,
>in each one of these ten tables exists one field that I have that to
>show together in a consultation with the first table. What of this two
>options goes to give the best performance?
>
>1) In client I make a consultation like this:
>select T1.FIELD1, T1.FIELD2, T1.FIELD3, ... T1.FIELDn,
> T2.FIELD2, T3.FIELD2, T4.FIELD2, T5.FIELD2,
> T6.FIELD2, T7.FIELD2, T8.FIELD2, T9.FIELD2,
> T10.FIELD2 from TABLE1 T1 left join TABLE2 T2 on T2.FIELD1 =
>T1.FIELD1
> left join TABLE3 T3 on T3.FIELD1 = T1.FIELD2
> left join TABLE4 T4 on T4.FIELD1 = T1.FIELD3
> left join TABLE5 T5 on T5.FIELD1 = T1.FIELD4
> left join TABLE6 T6 on T6.FIELD1 = T1.FIELD5
> left join TABLE7 T7 on T7.FIELD1 = T1.FIELD6
> left join TABLE8 T8 on T8.FIELD1 = T1.FIELD7
> left join TABLE9 T9 on T9.FIELD1 = T1.FIELD8
> left join TABLE10 T10 on T10.FIELD1 = T1.FIELD9;
>
>2) In database a create a structure like this:
>
>create table TABLE2 (
>FIELD1 INTEGER,
>FIELD2 VARCHAR(30)
>);
>...(all other)...
>
>create table TABLE10 (
>FIELD1 INTEGER,
>FIELD2 VARCHAR(30)
>);
>
>create table TABLE1 (
>FIELD1 INTEGER,
>...
>FIELD9 INTEGER,
>FIELD10 computed by ((select T.FIELD2 from TABLE2 T where T.FIELD1 =
>TABLE1.FIELD1)) ,
>FIELD11 computed by ((select T.FIELD2 from TABLE3 T where T.FIELD1 =
>TABLE1.FIELD2)) ,
>...(all other),
>);
>
>And them in client I make:
>
>select * from TABLE1;
>
>Thanks in advantage.
>Edson
>
>
>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
>
>
>
>