Post reply

query with outer join to empty table runs slow unless i use a join hint

  • September 7, 2011 at 12:10 am

    #243332

    hi there,

    i have a query that runs in 6 seconds. the execution plan shows that a hash join is being used on a left outer join to a table that has no rows. when i use a join hint to specify the hash join, the query runs in 1 second.

    does anyone have any thoughts on what might be happening here? the obvious inference is that the sql engine is taking 5 seconds to think about what type of join to use. this seems a bit unlikely, especially for a table with no records.

    thanks

    rob

  • September 7, 2011 at 1:38 am

    #1377810

    Stats may be outdated or insufficient.

    You could:

    A) UPDATE STATISTICS

    B) Check if you need a multicolumn or filtered statistic, which are not created automatically by SQL Server.

    Also, can you post the actual execution plan?

    -- Gianluca Sartori

  • September 7, 2011 at 1:48 am

    #1377817

    rob mcnicol (9/7/2011)

    when i use a join hint to specify the hash join, the query runs in 1 second.

    Depends how you are specifying the hash join: OPTION (HASH JOIN) or LEFT OUTER HASH JOIN. The latter forces the order of joins for all the tables in the query. There will be a difference in the query plans produced to explain the performance difference.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 7, 2011 at 1:55 am

    #1377820

    SQLkiwi (9/7/2011)

    rob mcnicol (9/7/2011)

    when i use a join hint to specify the hash join, the query runs in 1 second.

    Depends how you are specifying the hash join: OPTION (HASH JOIN) or LEFT OUTER HASH JOIN. The latter forces the order of joins for all the tables in the query. There will be a difference in the query plans produced to explain the performance difference.

    Quite true. I suspect that the HASH hint changes the behaviour of the optimizer on a different join, not the one to the empty table.

    The execution plan would help a lot, though.

    -- Gianluca Sartori

  • September 7, 2011 at 9:07 pm

    #1378345

    hi paul/gianluca,

    thanks for your reponses.

    the syntax i am using is

    [font="System"] from TableOne t1 left outer hash join TableTwo t2[/font]

    this takes 1 second to run. TableTwo is empty. when i remove the join hint, the optimiser still chooses a hash join but the query takes 6 seconds.

    the database has auto create statistics and auto update statistics set to true. there are no filters on the statistics and little chance of being allowed to create them.

    much though i would like to, unfortunately i am not able to post the execution plans. i do note that they are quite different and that some of the other joins have been changed from loop joins to hash joins. this supports paul's suggestion that the join hint is affecting more than just that one join.

    does this mean that bol's description of a join hint is misleading?

    "Join hints specify that the query optimizer enforce a join strategy between two tables"

    this description of a join hint leads me to believe that it should only be the join with the join hint that is affected, yet in my case the use of the join hint is clearly altering other joins as well. i would understand this if i was changing the type of join used but all i am doing is specifying the same join type that the optimiser chose anyway.

    without posting the execution plans, i appreciate there is probably not much more we can usefully discuss. not to worry. in its current state the query runs fast enough so there's a limit to how much point there is in getting bogged down second-guessing it. i want to get this squared away in time for kickoff tomorrow - go the all-blacks woop woop! 🙂

  • September 8, 2011 at 2:07 am

    #1378394

    rob mcnicol (9/7/2011)

    does this mean that bol's description of a join hint is misleading? [...] this description of a join hint leads me to believe that it should only be the join with the join hint that is affected, yet in my case the use of the join hint is clearly altering other joins as well. i would understand this if i was changing the type of join used but all i am doing is specifying the same join type that the optimiser chose anyway.

    From http://msdn.microsoft.com/en-us/library/ms173815.aspx:

    If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query...

    Aside from that, there are other side-effects from the implied FORCE ORDER - the optimizer generally ends up searching the plan space in a different order and with different transformation rules available to it. So, in general, specifying a hint like this often will affect far more than the local join type and order.

    i want to get this squared away in time for kickoff tomorrow - go the all-blacks woop woop! 🙂

    Go the ABs yeah!

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 6 posts - 1 through 5 (of 5 total)

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