How Many LEFT JOIN is Too Many?

Any one know any facts and figures about maximum Left Joins allowed (or recommended) in one query?

I am running a MS SQL 2000 my database is full of relational data and most of my foreign keys (INT data type) are a Clustered Indexed, Usually I will only be pulling one record from collection of about a dozen tables, but the Database is expected to grow fast and become big.

Right now I have a Stored Proc that has eight(8) LEFT JOINs in it. My worry is that this query will kill me as the database approaches 50,000 records.

Lito50k?

That's nothing...

Ah, IDENTITY...

What are you getting from those other tables?

What do your indexes look like?|||In most cases I will be pulling a one whole record from Parent Table and 2 or 3 columns from a child table that match id_fk to parent auto increment id, but it will join approx 8-15 tables...

Here is my structure...

Parent Table
------
id [int] (auto increment, clustered index)
globalID [varchar(20)]
photo [varchar(100)]
notes [varchar(2048)]
status [tinyint]
date [datetime]
author [int]

Child Tables (more or less all look the same)
------
id [int] (auto increment)
id_fk [int] (clustered index)
some_val [varchar(50)]
date [datetime]
author [int]

so my result will look something like this
Result
----------
parent.id
parent.globalID
parent.photo
parent.notes
parent.status
parent.date
parent.author
child1.some_val
child1.date
child2.some_val
child2.date
child3.some_val
child3.date
... and so on|||I guess I would make sure I had covered indexes for these tables...

Here check this out:

http://www.sql-server-performance.com/jc_sql_server_quantative_analysis5.asp|||this is a great article thanks...
you mentioned covered inexes, are those just clustered index-to-clustered index relationships or what?

Lito|||Well they mention it briefly in the article, but a covered index is one where all of the required column are in the index...

For example

Let's say you have 2 tables

Table1(Col1, Col2, Col3)
Table2(Col1, Col2, Col3)

Table1's key is Col1, and Table2 is Col+Col2

Since they are PKs lets say they ar clustered indexed

So you do

SELECT a.Col1, b.Col2, b.Col3
FROM Table1 INNER JOIN Table2

SQL Server will use the index, find the rid, and hit the data page to get the data (it already knows about col1 and col2 from the index however)

BUT! Now Lets say you create another index for table2

Non unique index with cols col1,col2,col3

Now SQL Server will use the new index, and never have to go to the much larger data page. It's an index only operation.

That's a covered index.

Have a nice day.|||Thank You Brett,
you have been a great help.

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
>