Subject | Best performance |
---|---|
Author | Edson T. Marques |
Post date | 2005-05-03T18:53:28Z |
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
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