SQL Server Hash Joins versus Nested Loops

Question

Hi all,

---

Quick note

So, as I was writing the problem below I found a way to fix it myself. I thought I'd still post the question because:

1. Someone might find it useful.
2. I don't understand too much why it works.

Anyway the fixed code is at the very bottom.

---

I originally wrote:

I've spent ages googling this and can find lots of related answers, but none that exactly match my question.

I run the code below against a SQL Server (10) database and it executes very fast. The execution plan it uses involves a hash join.

Then I run it again, but this time uncomment the first two lines (DECLARE and SET lines), and also delete the '+1' beside y.[in date] and uncomment the '+ @COUNTER'. Now the query takes ages (upon ages) to complete - the execution plan instead using nested loops. Note I'm still just adding one to the date, but using a variable instead of a constant.

The question is: can I make the query using @COUNTER use a hash join instead of a nested loop?

( A bit of background: What I'm trying to do is loosely match x.[in date] and y.[in date] so that they match if they're within a specified number of days of each other. The number of days for the query to use is populated from a field in another table. I tried using datediff() first with abs() and less than, but I'm pretty sure that's going to always use nested loops. (It does when I try it anyway!)

I tried doing everything referred to in various parameter sniffing articles, but they didn't change things. Anyway I'm not running this as a stored procedure. I'm guessing there's something to do with an index on the [in date] field. )

-- DECLARE @COUNTER INT
-- SET @COUNTER = 1

BEGIN

    SELECT
        x.[line id]
        , y.[line id]

    FROM
        lines1 AS x
        JOIN lines2 AS y ON (

            x.[in date] = y.[in date] + 1 -- + @COUNTER
            AND x.[country] = y.[country]

        )

    WHERE
        x.[country] = 'USA'

END

---

Here's the fixed code:

WITH test_tbl AS (
    SELECT -1 AS [col]
    UNION ALL
    SELECT 0 AS [col]
    UNION ALL
    SELECT 1 AS [col]
)

SELECT
    x.[line id]
    , y.[line id]

FROM
    lines1 AS x
    CROSS JOIN test_tbl AS z
    JOIN lines2 AS y ON (
        x.[in date] = y.[in date] + z.[col]
        AND x.[country] = y.[country]
    )

WHERE
    x.[country] = 'USA'

---

Obviously I'll have to figure out how to populate the temporary table on the fly, instead of using the WITH and UNION ALL's. I'm no SQL Server expert (obviously) but it shouldn't be too hard.

Answer 1

The question is: can I make the query using @COUNTER use a hash join instead of a nested loop?

Yes. You can use a join hint to force this:

INNER HASH JOIN