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:
- Someone might find it useful.
- I don't understand too much why it works.
Anyway the fixed code (see answers).
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
