SQL Server Performance

Query Tuning Multi-Join Select

Discussion in 'Performance Tuning for DBAs' started by MadforShoes, Jan 23, 2003.

  1. MadforShoes New Member

    This is my first post and it is a long one. I wanted to provide enough information for my question to be understandable. <br /><br />Another DBA and I were trying to tune this query. Here are the steps we followed:<br /><br />1. Ran the production query on a test server, no changes (no other users on box). <br />2. Ran the index tuning wizard and added the index suggestion from the wizard and re-ran query with new index (no other users). <br />3. Kept wizard index and added loop hint in query and ran (no other users). <br /><br />Here is what we found:<br /><br />1. Orginal query ran in 41 seconds consistently.<br />2. Query w/Wizard's index only ran in 35 seconds consistently.<br />3. Query w/wizard's index and loop hint ran in 21 seconds consistently.<br /><br />Here is my question. The logical I/O dropped for the worklist table when we added the index tuning wizard suggested index and the time was reduced by 6 seconds. Since there were a lot of non-nested joins happening we decided to add a loop hint and see what would happen. When we added a loop hint logical I/O went up for most tables yet the time the query ran in was considerably faster (19 sec). Maybe my logic is wrong but if we are on a test box that no one else is accessing shouldn't we be able to compare query analyzer time elapsed also? Everything I have read says you should look at logical I/O before and after. Below is our query, list of existing table indexes, index we added from wizard suggestion, and results of SHOWPLAN_TEXT and Statistics I/O from each 3 runs. <br /><br />Here is the query and existing indexes on the joining tables. I provided all indexes on the tables, not just the ones involved in the query. <br /><br />Any insight you could give us on this would be helpful. I hope I did not post too much info. <br /><br />thanks<br /><br />=======================QUERY=========================================<br />dbcc dropcleanbuffers<br />go<br />dbcc freeproccache<br />go<br /><br /><br />DECLARE @ClientMailGroup varchar(20)<br />SET @ClientMailGroup = 'HAR'<br /><br />select L.keyid, A.[sequence], L.LOANNO, A.send, A.sendtime, A.topic, A.genproid, L.vendorid, L.actiontype, l.vendornum, A.[from] as 'SENDER', <br />u.[NAME] <br />,v.COMPANY AS 'COMPANYNAME'<br />from WORKLIST W (nolock)<br />left outer join AT2CLT A ( NOLOCK ) on A.RECNO = W.RECORDNO<br />left outer join LSMASTER L (NOLOCK) on L.KEYID = A.KEYID<br />left outer join VENDORS V (NOLOCK) on L.VENDORID = V.VENDORID<br />left outer join USERS U (NOLOCK) on U.MAILGROUP = @ClientMailgroup and U.MAILID=A.genproid<br />where w.MAILGROUP = @ClientMailGroup and w.TABLENAME = 'AT2CLT'<br />order by w.DUE<br />=================END OF QUERY========================================<br /><br /><br />======================Existing Table Indexes============================ <br /><br />Vendors table:<br /><br />Clustered/PK - PK_vendors[VENDORID]<br />Non-clustered/Non Unique - StateServComp_IDX [STATESERV], [COMPANY]<br />Non-clustered/Non Unique - RecNo_IDX [RECNO]<br />Non-clustered/Non Unique - MailGroup_IDX [MAILGROUP]<br />Non-clustered/Non Unique - Company_IDX [COMPANY]<br />Non-clustered/Non Unique - Reference_IDX [REFERENCE]<br /> -----------------------------------------------------<br /><br />Users table:<br /><br />Non-clustered/Non Unique - USERS2 [MAILGROUP]<br />Non-clustered/Non Unique - RecNo_IDX [RECNO]<br />Non-clustered/Non Unique - MailGroupMailID_IDX [MAILGROUP], [MAILID]<br />Non-clustered/Non Unique - MailGroupName_IDX [MAILGROUP], [NAME]<br />Non-clustered/Non Unique - MailGroupUserId_Idx [MAILGROUP], [USERID]<br />Non-clustered/Non Unique - Name_IDX [NAME]<br />Non-clustered/Non Unique -UserID_IDX [USERID]<br />-------------------------------------------------------<br /><br />Worklist table:<br /><br />Non-clustered/Non Unique - MailGroupTableName_Idx [MailGroup], [TableName]<br />Non-clustered/Non Unique - Recno_Idx [RecNo]<br />Non-clustered/Non Unique - RecordNoTable_IDX [RecordNo], [TableName]<br />Non-clustered/Non Unique - KeyIdTable_Idx [KeyId], [TableName]<br />--------------------------------------------------------<br /><br />LSMaster table:<br /><br />Non-clustered/Non Unique - IXC_Lsmaster [KEYID], [VENDORID]<br />Non-clustered/Non Unique - RecNo_IDX [RECNO]<br />Non-clustered/Non Unique - Vendors_IDX [VENDORID]<br />Non-clustered/Non Unique - Client_IDX [CLIENTID]<br />Non-clustered/Non Unique - LoanNo_IDX [LOANNO]<br />Non-clustered/Non Unique - Street1_IDX [STREET1]<br />Non-clustered/Non Unique - InvLoanNo_IDX [INVLOANNO]<br />Non-clustered/Non Unique - ServClose_IDX [SERVCLOSE]<br />Non-clustered/Non Unique - VendorNum_IDX [VENDORNUM]<br />Non-clustered/Non Unique - Curtrustor_IDX [CURTRUSTOR]<br />Non-clustered/Non Unique - InvestID_IDX [INVESTID]<br />Non-clustered/Non Unique - tIDX_LSMASTER_KEYID_CLIENTID [KEYID], [CLIENTID]<br />Non-clustered/Non Unique - tIDX_LSMASTER_STATE_CLOSE [STATE], [CLOSE]<br />Non-clustered/Non Unique - tIDX_LSMASTER_COMPOSITE [CLIENTID], [VENDORNUM], [KEYID], [ACTIONTYPE], [LOANNO]<br />Non-clustered/Non Unique - LastModified_IDX [LastModified]) <br />Non-clustered/Non Unique - IDX_TSBodyAtt [VENDORID], [INVESTID], [ACTIONTYPE]<br />------------------------------------------------------------<br /><br />AT2CLT table:<br /><br />Clustered/PK - PK_AT2CLT [KEYID], [SEQUENCE]<br />Non-clustered/Non Unique - RecNo_IDX [RECNO]<br />Non-clustered/Non Unique - Readon_NoGen_IDX [READON], [KEYID]<br />Non-clustered/Non Unique - ReadOn_Gen_IDX [READON], [GENPROID], [KEYID]<br />Non-clustered/Non Unique - Send_IDX [SEND]<br />Non-clustered/Non Unique - LastModified_IDX [LastModified]<br /><br />=====================END OF EXISTING TABLE INDEXES=============<br /><br />=================Index Tuning Wizard Index Added===============<br />NONCLUSTERED/Non Unique WORKLIST24 [MailGroup] ASC, [TableName] ASC, [RecordNo] ASC, [Due] ASC<br /><br />==================End of Tuning Wizard Index Added=============<br /><br /><br />=======================STATISTICS IO RESULTS=====================<br /><br /><br />-----------------STATISTIC IO: Original Run--------------<br /><br />Query Analyzer time: 41 Seconds <br /><br /><br />Table 'Worktable'. Scan count 1501, <font color="red">logical reads 1652</font id="red">, physical reads 0, read-ahead reads 0.<br />Table 'USERS'. Scan count 2, <font color="red">logical reads 6</font id="red">, physical reads 2, read-ahead reads 4.<br />Table 'vendors'. Scan count 1167, <font color="red">logical reads 2334</font id="red">, physical reads 30, read-ahead reads 0.<br />Table 'LSMASTER'. Scan count 1167, <font color="red">logical reads 3501</font id="red">, physical reads 874, read-ahead reads 0.<br />Table 'AT2CLT'. Scan count 1502, <font color="red">logical reads 10523</font id="red">, physical reads 2138, read-ahead reads 0.<br />Table 'WORKLIST'. Scan count 1, <font color="red">logical reads 1512</font id="red">, physical reads 2, read-ahead reads 1236.<br /><br /><br /><br /><br />--------------STATISTIC IO: WIZARD INDEX ADDED-------------------<br /><br />Query Analyzer time: 35 Seconds <br /><br /><br />Table 'Worktable'. Scan count 1501, <font color="red">logical reads 1652</font id="red">, physical reads 0, read-ahead reads 0.<br />Table 'USERS'. Scan count 2, <font color="red">logical reads 6</font id="red">, physical reads 2, read-ahead reads 4.<br />Table 'vendors'. Scan count 1167, <font color="red">logical reads 2334</font id="red">, physical reads 30, read-ahead reads 0.<br />Table 'LSMASTER'. Scan count 1167, <font color="red">logical reads 3501</font id="red">, physical reads 874, read-ahead reads 0.<br />Table 'AT2CLT'. Scan count 1502, <font color="red">logical reads 10523</font id="red">, physical reads 2138, read-ahead reads 0.<br />Table 'WORKLIST'. Scan count 1, <font color="red">logical reads 11</font id="red">, physical reads 0, read-ahead reads 0.<br /><br /><br /><br /><br />--------------STATISTIC IO: WIZARD INDEX/LOOP HINT ADDED-----------<br /><br />Query Analyzer time: 19 Seconds <br /><br /><br />Table 'Worktable'. Scan count 1501, <font color="red">logical reads 1652</font id="red">, physical reads 0, read-ahead reads 0.<br />Table 'USERS'. Scan count 2, <font color="red">logical reads 6</font id="red">, physical reads 2, read-ahead reads 4.<br />Table 'vendors'. Scan count 1502, <font color="red">logical reads 3184</font id="red">, physical reads 16, read-ahead reads 14.<br />Table 'LSMASTER'. Scan count 1502, <font color="red">logical reads 7690</font id="red">, physical reads 76, read-ahead reads 857.<br />Table 'AT2CLT'. Scan count 1502, <font color="red">logical reads 18551</font id="red">, physical reads 7, read-ahead reads 2131.<br />Table 'WORKLIST'. Scan count 1, <font color="red">logical reads 11</font id="red">, physical reads 0, read-ahead reads 0.<br /><br /><font color="blue"><br />Note: I have not idea why the frown faces are showing up in my showplan. </font id="blue"><br /><br />==================SHOWPLAN_TEXT ORIGINAL RUN ======================<br /><br />StmtText <br />-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[RecordNo]))<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[Due] ASC))<br /> | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1000]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[WORKLIST] AS [W]))<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[WORKLIST].[MailGroupTableName_Idx] AS [W]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[MailGroup]=[@ClientMailGroup] AND [W].[TableName]='AT2CLT') ORDERED FORWARD)<br /> |--Nested Loops(Left Outer Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[GENPROID]=<U>.[MAILID]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[KEYID]))<br /> | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[AT2CLT] AS [A]))<br /> | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[AT2CLT].[RecNo_IDX] AS [A]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[RECNO]=[W].[RecordNo]) ORDERED FORWARD)<br /> | |--Hash Match(Cache, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[KEYID]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[KEYID]=[A].[KEYID]))<br /> | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[L].[VENDORID]))<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[LSMASTER].[PK_LSMASTER] AS [L]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[L].[KEYID]=[A].[KEYID]) ORDERED FORWARD)<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[vendors].[PK_vendors] AS [V]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[V].[VENDORID]=[L].[VENDORID]) ORDERED FORWARD)<br /> |--Table Spool<br /> |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1004])=([Bmk1004]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1004]=[Bmk1004]))<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[USERS].[MailGroupMailID_IDX] AS <U>), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' /><U>.[MAILGROUP]=[@ClientMailGroup]) ORDERED FORWARD)<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[USERS].[MailGroupName_IDX] AS <U>), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' /><U>.[MAILGROUP]=[@ClientMailGroup]) ORDERED FORWARD)<br /><br /><br /><br />==============SHOWPLAN_TEXT WIZARD INDEX ADDED====================<br /><br />StmtText <br />--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[RecordNo]))<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[Due] ASC))<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[WORKLIST].[WORKLIST24] AS [W]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[MailGroup]=[@ClientMailGroup] AND [W].[TableName]='AT2CLT') ORDERED FORWARD)<br /> |--Nested Loops(Left Outer Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[GENPROID]=<U>.[MAILID]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[KEYID]))<br /> | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[AT2CLT] AS [A]))<br /> | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[AT2CLT].[RecNo_IDX] AS [A]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[RECNO]=[W].[RecordNo]) ORDERED FORWARD)<br /> | |--Hash Match(Cache, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[KEYID]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[KEYID]=[A].[KEYID]))<br /> | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[L].[VENDORID]))<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[LSMASTER].[PK_LSMASTER] AS [L]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[L].[KEYID]=[A].[KEYID]) ORDERED FORWARD)<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[vendors].[PK_vendors] AS [V]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[V].[VENDORID]=[L].[VENDORID]) ORDERED FORWARD)<br /> |--Table Spool<br /> |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1004])=([Bmk1004]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1004]=[Bmk1004]))<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[USERS].[MailGroupMailID_IDX] AS <U>), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' /><U>.[MAILGROUP]=[@ClientMailGroup]) ORDERED FORWARD)<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[USERS].[MailGroupName_IDX] AS <U>), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' /><U>.[MAILGROUP]=[@ClientMailGroup]) ORDERED FORWARD)<br /><br />(15 row(s) affected)<br /><br /><br /><br /><br /><br />================SHOWPLAN_TEXT WIZARD INDEX/LOOP HINT ADDED=========<br /><br />StmtText <br /><br />----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br /> |--Nested Loops(Left Outer Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[GENPROID]=<U>.[MAILID]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[L].[VENDORID]) WITH PREFETCH)<br /> | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[KEYID]) WITH PREFETCH)<br /> | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[AT2CLT] AS [A]))<br /> | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[RecordNo]) WITH PREFETCH)<br /> | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[Due] ASC))<br /> | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[WORKLIST].[WORKLIST24] AS [W]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[W].[MailGroup]=[@ClientMailGroup] AND [W].[TableName]='AT2CLT') ORDERED FORWARD)<br /> | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[AT2CLT].[RecNo_IDX] AS [A]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[RECNO]=[W].[RecordNo]) ORDERED FORWARD)<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[LSMASTER].[PK_LSMASTER] AS [L]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[L].[KEYID]=[A].[KEYID]) ORDERED FORWARD)<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[vendors].[PK_vendors] AS [V]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[V].[VENDORID]=[L].[VENDORID]) ORDERED FORWARD)<br /> |--Table Spool<br /> |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1004])=([Bmk1004]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1004]=[Bmk1004]))<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[USERS].[MailGroupMailID_IDX] AS <U>), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' /><U>.[MAILGROUP]=[@ClientMailGroup]) ORDERED FORWARD)<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Lenstar].[dbo].[USERS].[MailGroupName_IDX] AS <U>), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' /><U>.[MAILGROUP]=[@ClientMailGroup]) ORDERED FORWARD)<br /><br /><br /><br />
  2. bradmcgehee New Member

    While I/O is very important for most queries, it isn't the only component that affects a query's performance. CPU speed also plays a part. Because of this, it is possible for a query with greater I/O than another query to take less time to run (because the query with the greater I/O used less CPU resources), and I think this is what is happening in this query.

    For example, a hash-based JOIN, as you have in plan called "SHOWPLAN_TEXT WIZARD INDEX ADDED" is much more CPU intensive than the loop-based JOIN in the "SHOWPLAN_TEXT WIZARD INDEX/LOOP HINT ADDED" plan. This may or may not account for all the performance gain you got, but is should explain some of it.

    When it comes right down to it, the query running the fastest is the one you want to go with. But if you do use a hint, you will want to revisit this query from time to time to see if it still is acting like you expect, as it is possible as the data distribution changes in your tables, that at some point this hint may not longer be useful. But the only way to know for sure is to watch the queries performance over time.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. sqljunkie New Member

    I'd be interested to see how this query performs when multiple copies of it are running or when the system is under a regular workload.
    I've always been under the impression joins were ranked best performing as follows:
    1. Hash
    2. Merge
    3. Nested Loops

    For each row selected in one table the Nested Loops join is comparing every row selected from the second table. That is why you're seeing higher logical reads. Merge join uses sorted input so there is a little less scanning and the Hash join makes a hash table so there is the least amount of I/O. Check out BOL for info on different joins.
    There is a chance with the loops join you could see I/O hotspots or bottlenecks.
  4. bradmcgehee New Member

    According to Microsoft, JOIN types perform in this order, from fastest to slowest:

    Loop
    Hash
    Merge

    I have not personally tested this thoroughly, although I have experimented with LOOP and HASH joins in limited cases, and LOOP joins always came out faster.

    I would like to hear more from others on their experience in this area.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. sqljunkie New Member

    Brad, what is your MSFT source that ranks in that order?
  6. bradmcgehee New Member

    In the Books Online, there is a topic called "Advanced Query Tuning Concepts" that discusses this. Also, in BOL, is another article called "Understanding Nested Loops Joins", which has some additional information. In my above post, I generalized somewhat, as you will see in the BOL, there are certain conditions where one particular method is generally better than another (i.e. the size of the tables being joined).

    In the book, "Inside Microsoft SQL Server 2000,", author Kalen Delaney covers these topics very well in pages 839 - 845. In generaly, what she says is that loop joins are usually picked by the query optimizer when there are useful indexes available. But if there are not useful indexes, then a hash join if often the next choice of the query optimizer, unless the two joined tables are both sorted on the join column, then a merge join is often done. She also says that both hashing and merging are both much more memory intensive than loops.

    It would be great if someone had some time to verify what has been published. As I mentioned before, in the few cases where I have tested it, the Loop join was faster than the Hash join. In my experiment, I used hints to force the join strategies.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  7. x002548 New Member

    It's my understanding (could be wrong of course) but if you continue to re-run a query over and over, the results get stored in buffer and will give you fatser results than if the buffer did not contain the results. Any one have any info on this, or am I missing the boat?

    I would suggest however to try and optimize the query. Is 1 patricular table a driver? What are the number of rows in each table?

    Brett
  8. x002548 New Member

    You'll have to make sure the results are the same, but how about this:

    SELECT *
    FROM ( SELECT *
    FROM AT2CLT A (NOLOCK)
    LEFT JOIN LSMASTER L (NOLOCK)
    ON L.KEYID = A.KEYID
    WHERE EXISTS (SELECT 1 FROM WORKLIST W (NOLOCK)
    WHERE A.RECNO = W.RECORDNO
    AND w.MAILGROUP = @ClientMailGroup
    AND w.TABLENAME = 'AT2CLT') AS XXX
    LEFT JOINVENDORS V (NOLOCK)
    ONXXX.VENDORID = V.VENDORID
    LEFT JOINUSERS U (NOLOCK)
    ONXXX.GENPROID = U.MAILID
    WHEREU.MAILGROUP = @ClientMailgroup


    Good Luck

    Brett

    PS Why are you ordering by something not in your select? Also make sure you replace the Select *'s with the fields you need.
  9. bradmcgehee New Member

    In regards to the question about the buffer being reused, this is true. Once data is added to the buffer for use by a query, it will stay there a while and can be reused. But if the data is not reused soon, and if more room is necessary for other data that needs to be added to the buffer, then it will be removed.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page