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.