登入
Microsoft.com
 
Microsoft
 
 
 

none
Question about using JOIN RRS feed

 > 

  • 問題

  • Question
    登入以投票
    0
    登入以投票
    你好

    我有一個關於 JOIN 既問題
    不太清楚
    希望有朋友可以解釋一下
    我想知道當你用JOIN 時
    E.G.
    SELECT *
    FROM TBL_A A
    LEFT JOIN TBL_B B
    ON A.ID = B.ID
    WHERE A.VALUE > B.RULEVALUE  AND A.TIMESTAMP >'07/07/2008'
    時
    如果A 的TABLE 有很多RECORD
    而 B 的TABLE STORE 了 一些 RULE 之類的 RECORD

    而 A 的RECORD 亦都要MATCH 一些 BASIC 既 CRITERIA 先可以進行 JOIN 時
    我應該放個CRITERIA 在 JOIN 果個CAUSE 還是 WHERE CAUSE 呢
    JOIN 這個STATEMENT 時會JOIN 崈2個TABLE 後先會RUN WHERE CAUSE?
    E.G. JOIN 了所有 MATCH RECORD 再用 WHERE CAUSE 選擇要果D?
    還是 行了 WHERE 先?

    我應該張上面的QUERY 改為下面一條會好D 嗎?

    SELECT *
    FROM TBL_A A
    LEFT JOIN TBL_B B
    ON A.ID = B.ID     AND A.TIMESTAMP >'07/07/2008'
    WHERE A.VALUE > B.RULEVALUE 

    EXECUTE 的時間好似 無乜分別
    我通常都會放果個STATEMENT 係 WHERE CAUSE 度
    唔知會唔會唔係咁好
    所以希望大家可以話比D ADVICE 我 EXPLAIN 邊個方法會EFFICIENT D

    謝謝大家

    2008年7月29日 下午 09:32
    |

解答

  • Question
    登入以投票
    0
    登入以投票

    First, I cannot view the image you given in your provided link, seems that it is removed.

     

    Second, Well, no matter where you put the criteria into Join or Where, they are using the same way to filter it from the execution plan as you can see the execution Plan are similiar and the time are close.

     

    You may try to create one index in the StartTime column to see if there is any improvement.

     

    2008年10月22日 上午 10:04
    |

所有回覆

  • Question
    登入以投票
    0
    登入以投票

    This depends on the join's condition is a Key or index.

    If it is, then you could apply it from the join criteria than where.

     

    Or you may try to run 2 query in your DB with your DB design, save the execution plans of them, save here and let's discuss directly with your execution plans.

    2008年7月30日 上午 05:03
    |
  • Question
    登入以投票
    0
    登入以投票
    Hi Ken,

    Thanks for your information. The field that on the extra requirement has not been indexed.

    I have tried to run both query and see the actual Execution Plan, they looks the same

    When I put the criteria into the Join Cause, it is 4 second quicker (2:29)

    SELECT *
    FROM [Remote-SERVER].db.dbo.RefTbl e
    LEFT JOIN   Record_Tbl c
    ON c.CustomerCode = e.CustomerCode COLLATE DATABASE_DEFAULT AND e.OpField='Cost' AND (StartTime BETWEEN '18/07/2008' AND '24/07/2008')
    WHERE Period='200807'
     AND CAST(e.OpValue as money) <= c.Charge
     AND e.CustomerCode IS NOT NULL

    When I put the criteria into the Where Cause, it is 4 second quicker (2:33)
    SELECT *
    FROM [Remote-SERVER].db.dbo.RefTbl e
    LEFT JOIN   Record_Tbl c
    ON c.CustomerCode = e.CustomerCode COLLATE DATABASE_DEFAULT AND e.OpField='Cost'
    WHERE Period='200807'     AND (StartTime BETWEEN '18/07/2008' AND '24/07/2008')
     AND CAST(e.OpValue as money) <= c.Charge
     AND e.CustomerCode IS NOT NULL

    Please find the Execution Plan from the URL below
    http://lookforchi.freehostia.com/images/Plan.JPG

    Thanks in advance.
    2008年7月30日 上午 08:28
    |
  • Question
    登入以投票
    0
    登入以投票

    First, I cannot view the image you given in your provided link, seems that it is removed.

     

    Second, Well, no matter where you put the criteria into Join or Where, they are using the same way to filter it from the execution plan as you can see the execution Plan are similiar and the time are close.

     

    You may try to create one index in the StartTime column to see if there is any improvement.

     

    2008年10月22日 上午 10:04
    |