SELECT subquery within IN statement hanging SQL Server

Question

curious about this behavior from SQL Server.

This query produces results very quickly, just as I'd expect:

SELECT *
FROM dbo.v_View1 View1 FULL OUTER JOIN
    dbo.v_View2 View2 ON View1.Portfolio = View2.Portfolio AND 
    View1.AsOfDate = View2.AsOfDate
where (View1.AsOfDate IN (NULL, '20130717'))

However, I don't want to have a static date in there, so I replaced it with a subquery. Unfortunately, the longest I've waited for this query to execute is 5 minutes before I cancelled it, so I don't know if it actually would get me the data I want:

SELECT *
FROM dbo.v_View1 View1 FULL OUTER JOIN
     dbo.v_View2 View2 ON View1.Portfolio = View2.Portfolio AND 
     View1.AsOfDate = View2.AsOfDate
where (View1.AsOfDate IN (NULL, (SELECT MAX(AsOfDate) FROM dbo.v_View1)))

I've resorted to declaring a variable, setting it with the subquery from above, and using that in the IN statement, which works as expected and runs just about as quickly as the original query.

I know I'm doing something wrong or missing something (probably both) - what is it? I'd like to have the subquery within the IN statement, or at least be able to run this as a view without variables. Thanks!

Answer 1

I suspect the query optimizer is doing something very strange because the naive implementation involves two scans on v_View1 perhaps the optimizer is failing to realize that the subquery SELECT MAX(AsOfDate) ... is going to be the same for each row. I suspect it might be failing to realize that the subquery is not correlated to each row and thus running it for every row of your result set. Given the full outer join, with a lot of data that means a lot of unnecessary table scans.

The simple solution is:

DECLARE @MaxAsOfDate datetime;
SET @MaxAsOfDate = (SELECT MAX(AsOfDate) FROM dbo.v_View1)

SELECT *
FROM dbo.v_View1 View1 FULL OUTER JOIN
    dbo.v_View2 View2 ON View1.Portfolio = View2.Portfolio AND 
    View1.AsOfDate = View2.AsOfDate
where (View1.AsOfDate IN (NULL, @MaxAsOfDate))

And that will force the subquery to run once, store the result in the variable, and then use that for the following query.