Professional Web Applications Themes

Very Slow INNER JOIN - Microsoft SQL / MS SQL Server

This is the current query that I am running: SELECT DISTINCT VE.DoentId, DL.Truck_Num, DL.Trailer_Num, DL.Trailer2_Num, DL.Trailer3_Num, UDL.Image_Name, UDL.Company_Driver, UDL.Company_Co_Driver FROM ValidationError VE (NOLOCK) INNER JOIN DL (NOLOCK) ON DL.DL_ID = VE.DoentId INNER JOIN UNI_DL UDL (NOLOCK) ON UDL.DL_ID = VE.DoentId WHERE VE.ErrorStatusId = 1 ORDER BY VE.DoentId ALL join fields are indexed(Non-Clustered). The Clustered Indexes are Identity fields and not referenced in this query. This query returns 15,000 row recordset and takes about 20 seconds to do it. The funny thing is though is when I just use the first two columns in the SELECT rather than use 8, the ...

  1. #1

    Default Very Slow INNER JOIN

    This is the current query that I am running:

    SELECT DISTINCT VE.DoentId, DL.Truck_Num, DL.Trailer_Num,
    DL.Trailer2_Num, DL.Trailer3_Num, UDL.Image_Name, UDL.Company_Driver,
    UDL.Company_Co_Driver
    FROM ValidationError VE (NOLOCK)
    INNER JOIN DL (NOLOCK) ON DL.DL_ID = VE.DoentId
    INNER JOIN UNI_DL UDL (NOLOCK) ON UDL.DL_ID = VE.DoentId
    WHERE VE.ErrorStatusId = 1
    ORDER BY VE.DoentId

    ALL join fields are indexed(Non-Clustered). The Clustered Indexes are
    Identity fields and not referenced in this query. This query returns
    15,000 row recordset and takes about 20 seconds to do it. The funny
    thing is though is when I just use the first two columns in the SELECT
    rather than use 8, the same number of rows returns in just under 4
    seconds. The problem is that I need all 8 coulmns though. Any ideas on
    how to speed this up? Thanks :)
    Travis Guest

  2. #2

    Default Re: Very Slow INNER JOIN


    "Travis L. Alltop" <com> wrote in message
    news:google.com... 

    Ahh, this is because SQL will omit a join if you don't read any columns
    from the joined table, and the join criteria cannot add or subtract a row
    from the result. This is the case for outer joins and for inner joins
    across foreign keys. It must be the case that UNI_DL.DL_ID is unique, and
    referenced by ValidationError.DoentID.

    So the problem is with joining UNI_DL. Look at the execution plan to see
    how it differs between the 2 queries.

    David


    David Guest

  3. #3

    Default Re: Very Slow INNER JOIN

    Travis,

    Please post DDL so people don't have to your tables, keys, indexes, etc.
    DDL will always be more accurate and more complete than any narrative.
    For example: are you saying that table DL has a clustered identity
    column that is not the primary key?

    If no columns are selected from table UNI_DL, then the query can be
    satisfied by accessing the column index. If other columns are selected,
    then bookmark lookups, a clustered index scan (if one exists) or a table
    scan (if no clustered index exists) are needed. This is likely to be
    more expensive than accessing the (smaller) nonclustered index.
    Have you checked that the index scan of both query plans is nonclustered
    (or clustered)? If one is clustered, and the other nonclustered, then
    that is a significant difference.

    You are likely to increase performance by makeing the primary key of
    table DL clustered. Also, if ValidationError.ErrorStatusId has high
    selectivity, then adding an index on ValidationError(ErrorStatusID,
    DoentID) might help.

    Hope this helps,
    Gert-Jan


    Travis Alltop wrote: 
    Gert-Jan Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Replies: 2
    Last Post: September 18th, 09:59 PM
  3. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139