Need to understand the drastic difference in execution plan between two queries

Question

I have 2 queries. select guid=NEWID() into #guid

--update statistics #guid with all
--create statistics s1 on #guid(guid)

select * from Party a join #guid b on a.[Party_GUID] = b.guid --first select
select * from Party a join #guid b on a.[Party_GUID] = b.guid or b.guid is null --second select

drop table #guid

There is an index on Party.Party_GUID. The query execution plan for the first select is excellant. For the second select statement is horrible. I need to understand the reason for this. There is only one row in the #guid table and it has a value. So the query optimizer should be creating a similar execution plan for the second query too. Am I expecting too much of the optimizer ? I have tried this on 2008R2 as well as a 2012.

Admittedly this is a contrived reproduction of an issue that I am currently encountering in our system. The developers have coded a stored procedure with a table valued parameter which holds various combination of search values to be applied on a table. E.g the table valued parameter can have 3 nullable fields, GUID, LastName, FirstName and the application can populate it with values ((N'89241068-7068-4728-9CD0-A565FC2BFDEB', null, null), (null, "smith", "john"), (null, null, "jane")). The expectation is that the stored procedure apply it as a filter. e.g select * from Party a join @tablevar b on a.Party_GUID = b.guid OR b.guid is null and (a.LastName = b.LastName or b.LastName is null) and (a.FirstName = b.FirstName or b.FirstName is null)

One can argue that this is tough query to optimize but it-is-what-it-is currently and am trying to look at avenues to help the optimizer come up with the best query plans. I do understand that some inputs can result in a horrible plan. What I am trying to understand is why the "OR is null" clause degrades the execution plan so much even if there are indexes on the table.

I know there are dbcc traceflags which will help me understand why the optimizer chose a particular plan but i find those hard to comprehend.

Any help appreciated.

Answer 1

To summarize: the application sends a TVP where each row is a set of search parameters, where a null value in the TVP functioning as a "wildcard" indicate no filtering on that attribute. The goal of the query is to return all the rows in the target table that match any of the rows in the input TVF.

So if the TVP sends (Id=123,Name=null),(ID=null,Name='Joe'), the procedure should return all rows match either the first set of criteria or the second.

Am I expecting too much of the optimizer ?

Yes. For this to work well, the QO would neeed to create a seperate plan for each row in the input TVF, and it simply was never build to do that. For each row in the TVF a table scan will be required, as no single index can be used to evaluate the join criteria.

So you actually need to run a separate query for each row in the input TVF. You can cursor over them and load a temp table, reducing this to an iterative form of a classic dynamic search query, which you can use Dynamic SQL or OPTION RECOMPILE to get tailored execution plans.

This

What I am trying to understand is why the "OR is null" clause degrades the execution plan so much even if there are indexes on the table.

Is simple and not too relevant. In the sample you posted if b.guid is null, then the query returns every row in Party.

Answer 2

As already pointed out, comparing TVP to Temp Tables is not really possible, they aren't the same thing, so your query plan may not be the same as what you're seeing. However, assuming it is, the queries you're questioning:

select guid=NEWID() into #guid

select * from Party a join #guid b on a.[Party_GUID] = b.guid or b.guid is null --second select

You mention that the query is more complex, but in your example b.guid can never be null if the only record in #guid is a NEWID(). I don't think I've ever seen NEWID() return null, so you should just drop that part of the join.

Assuming that it as nulls in it, what do you want the result to return? The reason that your 2nd query is so slow and that you get a different plan is that if you have a null in #guid then you're asking SQL to return all records from Party, for every record in #guid that is null.

Example using Stack Overflow database:

CREATE TABLE #id (Id INT NULL)

INSERT INTO #id (Id) VALUES (1)

SELECT  *
FROM    dbo.VoteTypes
JOIN    #id
  ON    #id.Id = VoteTypes.Id
  OR    #id.Id IS NULL

This is the equivalent to your second query - find all records where ID = 1 or #id is null. It returns 1 record as expected because there are no null records in #id

However, if we now insert some NULLs into #id then we get some different results:

INSERT INTO #id (Id) VALUES ( NULL), (NULL)

This returns 31 records - where ID = 1, and then every single record from VoteTypes table twice - once for each null in #id.

This is not what you're trying to achieve. In your question, you describe that you want to match different columns based on what's passed in - you want to match on whatever is specified. You can do that with ORs:

CREATE TABLE #id (Id INT NULL, Name VARCHAR(50));

INSERT INTO #id (Id, Name)
VALUES
(1, NULL)
,(NULL, 'UpMod')
,(NULL, 'Close');

SELECT  *
FROM    dbo.VoteTypes
JOIN    #id
  ON    #id.Id = VoteTypes.Id
  OR    #id.Name = VoteTypes.Name;

This will return 3 rows - where ID = 1, Name = UpMod and Name = Close

If you only want to match Name when ID is null, then make that a condition on the OR (it's hard for me to tell without the original query, but I think your brackets are in the wrong spot):

SELECT  *
FROM    dbo.VoteTypes
JOIN    #id
  ON    #id.Id = VoteTypes.Id
  OR    (#id.Id IS NULL AND #id.Name = VoteTypes.Name);

What I have seen work better is having multiple queries that you union together. That way you can index all the search columns and the optimiser can choose the right index for each filter. So select where a.Party_GUID = b.guid, select where a.last_name = b.last_name and b.guid is null and select where a.first_name = b.first_name and b.guid is null and b.last_name is null. You can index for those 3 criteria pretty easily by themselves, and either union the results or insert them all into a temp table.