How many joins is too many?

Does anyone have any good sources of information that talk about how many
joins is too many? I've heard the number 4 or 5 thrown around but haven't
found any 'evidence' to back them up.You need to join whatever tables contain the information you need to
fullfill the case usage needs of the query. For a typical reporting
application, 5 tables would be the minimum required for even a simple
request and > 10 is common. In an online call center application, if user's
complain about performance of populating a customer information screen, then
you may want to consider if too much information is being displayed, and
eliminate unneeded joins. It basically has to do with how many data items
are required by the user's report or screen.
"Shane C" <Shane C@.discussions.microsoft.com> wrote in message
news:E1BA581A-AB3C-4AE6-9A79-C50F4D5EB944@.microsoft.com...
> Does anyone have any good sources of information that talk about how many
> joins is too many? I've heard the number 4 or 5 thrown around but haven't
> found any 'evidence' to back them up.
>|||Honestly, it depends on the tables and the data you are retrieving. The
less joins the better, as the database has to do less work, spend less time
optimizing, and is more likely to find the optimal path with fewer tables.
SQL server can process a ridiculous number of tables in a single query (100
or more?) so there is not really a limit there. Well, there is a limit, but
if you have 100 tables in a query, no one will ever be able to understand
it.
I have seen SQLs with more than 20 tables that ran quite efficiently, and
ones with 3 tables that ran very poorly. If your indexes and join logic are
set up correctly, performance should not be an issue. However, I have seen
(on Oracle) where an improperly configured database would spend 20 minutes
optimizing a query that ran in 3 seconds because there were too many tables.
A simple change to the database optimizer fixed this so it took milliseconds
to optimize the SQL and 3 seconds to return the results.
If you have very large tables, or tables without optimal indexes, or just
poorly written joins/where clauses, you will run into problems with much
fewer tables.
One thing that must be considered is how readable is the SQL. Your query
with 30 tables may perform well, but how long does it take a human to figure
out what is going on in order to maintain the query? In these cases,
splitting the query up into several views, then joining those views together
can give the same logical query, but with smaller pieces that are easier to
digest and maintain. IMO, this is the main consideration when asking how
many joins is too many, because if you can't maintain it later on, you are
shooting yourself in the foot.
"Shane C" <Shane C@.discussions.microsoft.com> wrote in message
news:E1BA581A-AB3C-4AE6-9A79-C50F4D5EB944@.microsoft.com...
> Does anyone have any good sources of information that talk about how many
> joins is too many? I've heard the number 4 or 5 thrown around but haven't
> found any 'evidence' to back them up.
>|||"Shane C" <Shane C@.discussions.microsoft.com> wrote in message
news:E1BA581A-AB3C-4AE6-9A79-C50F4D5EB944@.microsoft.com...
> Does anyone have any good sources of information that talk about how many
> joins is too many? I've heard the number 4 or 5 thrown around but haven't
> found any 'evidence' to back them up.
>
Any more than you need is too many and any less than you need is too few.
What's the point of putting an arbitrary number on it? Are you going to tell
your customers "Sorry, I can't do that because it takes more than 5 joins!"?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> What's the point of putting an arbitrary number on it? Are you going to
> tell your customers "Sorry, I can't do that because it takes more than 5
> joins!"?
If they come at me with a new request @. 4:45pm on a Friday afternoon, then
sure; that's what I tell'um. ;-)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OX1iI9zXGHA.456@.TK2MSFTNGP04.phx.gbl...
> "Shane C" <Shane C@.discussions.microsoft.com> wrote in message
> news:E1BA581A-AB3C-4AE6-9A79-C50F4D5EB944@.microsoft.com...
> Any more than you need is too many and any less than you need is too few.
> What's the point of putting an arbitrary number on it? Are you going to
> tell your customers "Sorry, I can't do that because it takes more than 5
> joins!"?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||To many joins indicates a poorly designed database.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:us%23yk1zXGHA.3448@.TK2MSFTNGP04.phx.gbl...
> Honestly, it depends on the tables and the data you are retrieving. The
> less joins the better, as the database has to do less work, spend less
time
> optimizing, and is more likely to find the optimal path with fewer
tables.
> SQL server can process a ridiculous number of tables in a single query
(100
> or more?) so there is not really a limit there. Well, there is a limit,
but
> if you have 100 tables in a query, no one will ever be able to understand
> it.
> I have seen SQLs with more than 20 tables that ran quite efficiently, and
> ones with 3 tables that ran very poorly. If your indexes and join logic
are
> set up correctly, performance should not be an issue. However, I have
seen
> (on Oracle) where an improperly configured database would spend 20 minutes
> optimizing a query that ran in 3 seconds because there were too many
tables.
> A simple change to the database optimizer fixed this so it took
milliseconds
> to optimize the SQL and 3 seconds to return the results.
> If you have very large tables, or tables without optimal indexes, or just
> poorly written joins/where clauses, you will run into problems with much
> fewer tables.
> One thing that must be considered is how readable is the SQL. Your query
> with 30 tables may perform well, but how long does it take a human to
figure
> out what is going on in order to maintain the query? In these cases,
> splitting the query up into several views, then joining those views
together
> can give the same logical query, but with smaller pieces that are easier
to
> digest and maintain. IMO, this is the main consideration when asking how
> many joins is too many, because if you can't maintain it later on, you are
> shooting yourself in the foot.
>
> "Shane C" <Shane C@.discussions.microsoft.com> wrote in message
> news:E1BA581A-AB3C-4AE6-9A79-C50F4D5EB944@.microsoft.com...
many
haven't
>|||Did you have an example in mind?
Using surrogates for foreign keys often means you'll require extra joins in
code compared to using the natural keys. So you could say that the use of
unnecessary surrogate keys is a poor design that results in unnecessary
joins. I still don't see how can say X joins may be caused by poor design.
What is "too many" in your opinion?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I can think of an example: attempting to implement object-oriented
concepts (like inheritance) into a relational database. I've
encounterd a database that was designed around the concept that
everything is an object, and every object has a name and a description.
So they took those two columns (and a surrogate key) and put them in
their own table.
So If I want to find out how many components are in a kit, I have to
make joiins at every level to ths original object table in order to get
the name. Even a simple query (such as retrieving a list of parts)
requries at least two tables (and probably three because they have a
parts table that just has a list of the surrogate keys for parts).
And, before I get deluged with suggestions on how to fix this, it's a
vendor database that supports a vendor application, so I have no
control over it's design or function; my job is to suck data out of it.
Entities within a database should be complete, and table structures
should be atomic unto themselves; just because two entities share a
common attribute (such as a name) doesn't mean that should split those
common attributes into their own table and do 1-1 joins.
Stu|||> ... just because two entities share a common attribute (such as a name)
> doesn't mean that should split those common attributes into their own
> table and do 1-1 joins.
Absolutely. When normalization is misunderstood one can quite soon end up
with the four table schema:
Entities <-- EntityAttributes --> Attributes
<-- EntityEntities [<-- Entities]
Now, go write a query. But there is still one possible level of abstraction
beyond the one described above. I call it the Zen model:
.
Nice schema, right?
Typical Zen query:
I need data... I feel data... I *am* data.
I'm just not sure whether MS SQL supports this today.
ML
http://milambda.blogspot.com/|||What is too many, what is too less. A denormalised database is poor design
not the amount of joins. I have a situation where I have over 15 tables, but
thats because the data that needs to be retrieved calss for it. Or would it
be better database design to put that into a flat table, better database
design'?
Robert
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:ePGSxb1XGHA.4424@.TK2MSFTNGP05.phx.gbl...
> To many joins indicates a poorly designed database.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:us%23yk1zXGHA.3448@.TK2MSFTNGP04.phx.gbl...
> time
> tables.
> (100
> but
understand
and
> are
> seen
minutes
> tables.
> milliseconds
just
query
> figure
> together
> to
how
are
> many
> haven't
>