Execution speed of joins

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 26 tables all with a common field. Is it faster to mjoin the first
table to each of the other 25 or table 1 to 2 thwen 2 to 3, 3 to 4, etc.
 
I have 26 tables all with a common field. Is it faster to mjoin the first
table to each of the other 25 or table 1 to 2 thwen 2 to 3, 3 to 4, etc.
Click to expand...

YOW.

Are these - what - all one-to-one relationships? If you have ten
fields per table you'll get 260 fields in the join: and it will fail
because a query can only have 255.

You've got much more serious problems than performance here! What
Entities do these tables represent? How are they in fact related?


John W. Vinson[MVP]
 
John,
I was posting this question sort of blindly for a student during class
break. I have now seen the database. The tables are one master and 25 tables
of test data. The common field is a date/time stamp so they put that field in
from the master table, then add the test data fields(3 per table) from the
other 25. Since the linked field always appears in each table, the question
now makes sense. I think option A is better but wasn't positive.
Chip
 
John,
I was posting this question sort of blindly for a student during class
break. I have now seen the database. The tables are one master and 25 tables
of test data. The common field is a date/time stamp so they put that field in
from the master table, then add the test data fields(3 per table) from the
other 25. Since the linked field always appears in each table, the question
now makes sense. I think option A is better but wasn't positive.
Chip
Click to expand...

Still sounds like a Really Bad Table Design. If the 25 tables
correspond to 25 different tests, all with the same table structure, a
much better design would be to have ONE test data table, with a field
to indicate which test.

Also, Date/Time is a dangerous choice of linking field. Since it's
stored as a Double Float, you could have a maintable record and a
child table record which appear the same to the second, but are in
fact different by a few microseconds, due to Double Float roundoff
error.

That said - the "sea urchin" model is probably better than the "chain"
model in any case (just as uni sushi is better, IMO, than sea
cucumber).

John W. Vinson[MVP]
 
I guess they import the tables from some other program. The tables are NOT
the same structure. They are just selecting the relevant fields from each
table. I also wondered about the link, but they seem to have not had any
issues. (Maybe they just don't realize it, but I would think it would have
been obvious to them.)
I would love them to give me a shot at reviewing and overhauling the database.
Anyway, thanks for the input.
 
Chip said:
I guess they import the tables from some other program. The tables are NOT
the same structure. They are just selecting the relevant fields from each
table.
Click to expand...

I agree with John Vinson that the design sounds bad, but perhaps that's
just due to my ignorance of the subject matter. One frequently finds
databases with structures far more complex than the data call for. But
if the data themselves have a complex structure, and if the people
maintaining the database are comfortable with that, then OK, they may be
doing the right thing.

But there still could be other ways to attack the problem. What is the
purpose of having a database at all? Usually, it's to simplify and
organize some collection of data. If the data are fully understood,
maybe even a computer isn't needed.

BTW, even if the database designers claimed that the ancillary Tables
have differing structures, I suspect that they may be grabbing similar
parts out of each. This could be done via Queries that could select the
relevant field(s) from each foreign Table and present them in a
consistent format, including a test-identifier field. Such Queries
could be combined (e.g., via a union query) into a single dataset that
would lend itself to further analysis, such as to average findings over
a specified collection of test types. The proposed current design
wouldn't make that a very easy task.

It sounds like (to use a spreadsheet analogy) choosing to compute a sum
as =(A1 + A2 + A3 + ... + A99) instead of =(SUM(A1:A99)). Either one
might work correctly, but the second is more easily maintainable, not to
mention easier to communicate.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

[...]
I would love them to give me a shot at reviewing and overhauling the database.
Anyway, thanks for the input.

:
[...]
Still sounds like a Really Bad Table Design. If the 25 tables
correspond to 25 different tests, all with the same table structure, a
much better design would be to have ONE test data table, with a field
to indicate which test.
[...]

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top