Post reply

Can we replace "inner hash join" with "inner join" in my quey, where to use "inner hash join" ?

  • May 14, 2012 at 1:53 am

    #274068

    scenario: Below query used in a procedures and takes 4 miniuts to execute it, when we replace "inner hash join" with "inner join" it will excecute in 1 sec , I have tested it in a UAT server.

    Question : Can I replace "inner hash join" with "inner join" should it effect on data manupulation ?? Where to use inner hash join??

    select rnt_tran_syscode

    from DTL_transactions trans

    inner hash join HDR_customer_master tmpcm

    on trans.client_syscode = tmpcm.cust_syscode

    inner hash join TEMP_rnt_trans tmptr

    on tmptr.foliono = trans.folio_no

    And ( tmptr.tran_status = 'A' or tmptr.TRAN_STATUS is null)

    Here DTL_transactions is a transaction table having 17444912 records

    also index on key values like client_syscode, ..

    HDR_customer_master is a master table having 137567

    also index on key values like cust_syscode, ..

    and TEMP_rnt_trans is temporary table having 315984

    index on tran_status but not index on "foliono"

  • May 14, 2012 at 3:48 am

    #1486994

    jitendra.gupta1 89520 (5/14/2012)

    ...Question : Can I replace "inner hash join" with "inner join" should it effect on data manupulation ?? Where to use inner hash join??

    Answer:

    1) Yes, you can replace. It won't affect the functionality of the SP.

    2) When you have large unsorted data.

    BOL

    Caution:Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend only using hints as a last resort for experienced developers and database administrators.

  • May 14, 2012 at 10:54 am

    #1487261

    Yes you can and you should. The only time you should be using join hints is when you know exactly why the optimiser isn't choosing the join you want, you know exactly the effects that the join hint will have and you have tested extensively and seen that the join hint does improve performance in all cases.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply