Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Post Unanswered: 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.

    Lito

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    50k?

    That's nothing...

    Ah, IDENTITY....

    What are you getting from those other tables?

    What do your indexes look like?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    46
    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
    Last edited by lito; 02-26-04 at 16:21.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess I would make sure I had covered indexes for these tables....


    Here check this out:

    http://www.sql-server-performance.co..._analysis5.asp
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    46
    this is a great article thanks...
    you mentioned covered inexes, are those just clustered index-to-clustered index relationships or what?

    Lito

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Posts
    46
    Thank You Brett,
    you have been a great help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •